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);
}
private string GetSheetSuffix(TargetTableSheet sheet)
{
var suffix = "";
switch (sheet.Index)
{
case 1:
suffix = "FYJ";
break;
case 2:
suffix = "BLDK";
break;
case 3:
suffix = "YQ";
break;
case 4:
suffix = "ZQX";
break;
case 5:
suffix = "GZDK";
break;
default:
Logger.Error("Unknown sheet index: " + sheet.Index.ToString());
throw new Exception("Unknown sheet index: " + sheet.Index.ToString());
}
return(suffix);
}
private string GetInsertSql(SqlDataReader reader, TargetTableSheet sheet)
{
var fields = new StringBuilder();
var values = new StringBuilder();
fields.AppendFormat("[{0}]", sheet.Columns[0].Name);
values.Append(DataUtility.GetSqlValue(reader, 0));
for (int i = 1; i < sheet.Columns.Count; i++)
{
fields.AppendFormat(", [{0}]", sheet.Columns[i].Name);
if (i == 8)
{
values.Append(", '" + GetDates(reader[i].ToString()) + "'");
}
else
{
values.Append(", " + DataUtility.GetSqlValue(reader, i));
}
}
var sql = new StringBuilder();
sql.AppendLine(string.Format("INSERT INTO [{0}$] ({1})", sheet.Name, fields.ToString()));
sql.AppendLine(string.Format("SELECT {0}", values.ToString()));
return(sql.ToString());
}
private string GetSheetSuffix(TargetTableSheet sheet)
{
var suffix = "";
switch (sheet.Index) {
case 1:
suffix = "FYJ";
break;
case 2:
suffix = "BLDK";
break;
case 3:
suffix = "YQ";
break;
case 4:
suffix = "ZQX";
break;
case 5:
suffix = "GZDK";
break;
default:
Logger.Error("Unknown sheet index: " + sheet.Index.ToString());
throw new Exception("Unknown sheet index: " + sheet.Index.ToString());
}
return suffix;
}
private string PopulateSheet3(string filePath, TargetTableSheet sheet)
{
var result = "";
var dao = new SqlDbHelper();
var sql = string.Format("EXEC spX_BLDKJC_X_3 '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
Logger.Debug("Running " + sql);
var table = dao.ExecuteDataTable(sql);
result = ExcelHelper.PopulateX_BLDKJC_X_3(filePath, sheet, this.AsOfDate, table);
return result;
}
private string PopulateSheetVS(string filePath, TargetTableSheet sheet)
{
var result = "";
var dao = new SqlDbHelper();
var sql = string.Format("EXEC spX_WJFL_M_vs '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
Logger.Debug("Running " + sql);
var table = dao.ExecuteDataTable(sql);
result = ExcelHelper.PopulateX_WJFL_M_VS(filePath, sheet, this.AsOfDate, table);
return(result);
}
private string GetInsertSql(SqlDataReader reader, TargetTableSheet sheet, List<string> cols)
{
var fields = new StringBuilder();
var values = new StringBuilder();
fields.AppendFormat("[{0}]", cols[0]);
values.Append(DataUtility.GetSqlValue(reader, 0));
for (int i = 1; i < cols.Count; i++) {
fields.AppendFormat(", [{0}]", cols[i]);
values.Append(", " + DataUtility.GetSqlValue(reader, i));
}
var sql = new StringBuilder();
sql.AppendLine(string.Format("INSERT INTO [{0}$] ({1})", sheet.Name, fields.ToString()));
sql.AppendLine(string.Format("SELECT {0}", values.ToString()));
return sql.ToString();
}
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 string GetInsertSql(SqlDataReader reader, TargetTableSheet sheet, List <string> cols)
{
var fields = new StringBuilder();
var values = new StringBuilder();
fields.AppendFormat("[{0}]", cols[0]);
values.Append(DataUtility.GetSqlValue(reader, 0));
for (int i = 1; i < cols.Count; i++)
{
fields.AppendFormat(", [{0}]", cols[i]);
values.Append(", " + DataUtility.GetSqlValue(reader, i));
}
var sql = new StringBuilder();
sql.AppendLine(string.Format("INSERT INTO [{0}$] ({1})", sheet.Name, fields.ToString()));
sql.AppendLine(string.Format("SELECT {0}", values.ToString()));
return(sql.ToString());
}
private string PopulateSheet2(string filePath, TargetTableSheet sheet)
{
var result = "";
var dao = new SqlDbHelper();
var sql = string.Format("EXEC spX_CSHSX_M_2 '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
Logger.Debug("Running " + sql);
var table = dao.ExecuteDataTable(sql);
if (table != null)
{
result = ExcelHelper.PopulateX_CSHSX_M_2(filePath, sheet, this.AsOfDate, table);
}
else
{
result = "Procedure returned zero rows";
}
return(result);
}
private string GetInsertSql(SqlDataReader reader, TargetTableSheet sheet)
{
var fields = new StringBuilder();
var values = new StringBuilder();
fields.AppendFormat("[{0}]", sheet.Columns[0].Name);
values.Append(DataUtility.GetSqlValue(reader, 0));
for (int i = 1; i < sheet.Columns.Count; i++) {
fields.AppendFormat(", [{0}]", sheet.Columns[i].Name);
if (i == 8) {
values.Append(", '" + GetDates(reader[i].ToString()) + "'");
}
else {
values.Append(", " + DataUtility.GetSqlValue(reader, i));
}
}
var sql = new StringBuilder();
sql.AppendLine(string.Format("INSERT INTO [{0}$] ({1})", sheet.Name, fields.ToString()));
sql.AppendLine(string.Format("SELECT {0}", values.ToString()));
return sql.ToString();
}
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);
}
public static string PopulateX_CSHSX_M_1(string filePath, TargetTableSheet sheet, DateTime asOfDate, System.Data.DataTable dataTable)
{
logger.Debug("Populating X_CSHSX_M_1");
Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook theExcelBook = null;
Worksheet theSheet = null;
bool excelOpened = false;
try {
theExcelBook = theExcelApp.Workbooks.Open(filePath);
excelOpened = true;
theSheet = (Worksheet)theExcelBook.Sheets[sheet.Index];
int excelRow = 5;
for (int i = 0; i < dataTable.Rows.Count; i++) {
for (int j = 0; j < 18; j++) {
((Range)theSheet.Cells[excelRow, 3 + j]).Value2 = dataTable.Rows[i][2 + j];
}
excelRow++;
if (excelRow == 6) { // 1.按贷款担保方式
excelRow = 7;
}
else if (excelRow == 8) { // 1.2保证贷款 --留给excel公式计算
i++;
excelRow = 9;
}
else if (excelRow == 11) { // 2.按贷款逾期情况
excelRow = 12;
}
else if (excelRow == 15) { // 3.按贷款对象
excelRow = 16;
}
}
SubstituteReportHeader(theSheet, sheet, asOfDate);
theExcelBook.Save();
logger.Debug("Population done");
}
catch (Exception ex) {
logger.Error(ex);
throw;
}
finally {
if (excelOpened) {
theExcelBook.Close(false, null, null);
}
theExcelApp.Quit();
if (theSheet != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
}
if (theExcelBook != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
GC.Collect();
}
return string.Empty;
}
public static string PopulateX_CSHSX_M_4(string filePath, TargetTableSheet sheet, DateTime asOfDate, System.Data.DataTable dataTable)
{
logger.Debug("Populating X_CSHSX_M_4");
Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook theExcelBook = null;
Worksheet theSheet = null;
bool excelOpened = false;
try {
theExcelBook = theExcelApp.Workbooks.Open(filePath);
excelOpened = true;
theSheet = (Worksheet)theExcelBook.Sheets[sheet.Index];
int rowStartAt = 7;
for (int i = 0; i < dataTable.Rows.Count; i++) {
int excelColumn = 2;
for (int j = 0; j < dataTable.Columns.Count; j++) {
if (j == 6) { // 借款开始日
((Range)theSheet.Cells[rowStartAt + i, excelColumn]).Value2 = string.Format("{0}至{1}", ((DateTime)dataTable.Rows[i][j]).ToString("yyyy年MM月dd日"), ((DateTime)dataTable.Rows[i][j + 1]).ToString("yyyy年MM月dd日"));
j++; // 跳过借款到期日
}
else {
((Range)theSheet.Cells[rowStartAt + i, excelColumn]).Value2 = dataTable.Rows[i][j];
}
excelColumn++;
if (excelColumn == 8) { // 首付比例
excelColumn = 10;
}
else if (excelColumn == 12) { // 担保情况, L-Q列
excelColumn = 18;
}
}
}
SubstituteReportHeader(theSheet, sheet, asOfDate);
theExcelBook.Save();
logger.Debug("Population done");
}
catch (Exception ex) {
logger.Error(ex);
throw;
}
finally {
if (excelOpened) {
theExcelBook.Close(false, null, null);
}
theExcelApp.Quit();
if (theSheet != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
}
if (theExcelBook != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
GC.Collect();
}
return string.Empty;
}
private string PopulateSheet1(string filePath, TargetTableSheet sheet)
{
var result = "";
var dao = new SqlDbHelper();
var sql = string.Format("EXEC spX_CSHSX_M_1 '{0}'", this.AsOfDate.ToString("yyyyMMdd"));
Logger.Debug("Running " + sql);
var table = dao.ExecuteDataTable(sql);
if (table != null) {
result = ExcelHelper.PopulateX_CSHSX_M_1(filePath, sheet, this.AsOfDate, table);
}
else {
result = "Procedure returned zero rows";
}
return result;
}
public static bool SubstituteReportHeader(Worksheet theSheet, TargetTableSheet sheet, DateTime asOfDate)
{
return SubstituteReportHeader(theSheet, sheet, asOfDate, new DateTime(1900, 1, 1));
}
public static string PopulateSF6301_141(string filePath, TargetTableSheet sheet, DateTime asOfDate, System.Data.DataTable dataTable, System.Data.DataTable dataTable2)
{
logger.Debug("Populating SF6301_141");
Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook theExcelBook = null;
Worksheet theSheet = null;
bool excelOpened = false;
try {
theExcelBook = theExcelApp.Workbooks.Open(filePath);
excelOpened = true;
theSheet = (Worksheet)theExcelBook.Sheets[1];
int rowsBeforeStart = 5;
int r = 0;
for (int i = 1; i <= 21; i++) {
if (i == 2 || i == 3 || i == 8 || i == 13 || i == 17 || i == 19) {
continue;
}
for (int j = 1; j <= 7; j++) {
if (dataTable.Rows[r][j + 1] == DBNull.Value) {
((Range)theSheet.Cells[rowsBeforeStart + i, 2 + j]).Value2 = "0.00";
}
else {
((Range)theSheet.Cells[rowsBeforeStart + i, 2 + j]).Value2 = dataTable.Rows[r][j + 1];
}
}
r++;
}
// 授信户数
for (int j = 1; j <= 7; j++) {
((Range)theSheet.Cells[rowsBeforeStart + 25, 2 + j]).Value2 = dataTable2.Rows[0]["Count" + j.ToString()];
((Range)theSheet.Cells[rowsBeforeStart + 26, 2 + j]).Value2 = dataTable2.Rows[0]["Count" + j.ToString()];
}
SubstituteReportHeader(theSheet, sheet, asOfDate);
theExcelBook.Save();
logger.Debug("Population done");
}
catch (Exception ex) {
logger.Error(ex);
throw;
}
finally {
if (excelOpened) {
theExcelBook.Close(false, null, null);
}
theExcelApp.Quit();
if (theSheet != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
}
if (theExcelBook != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
GC.Collect();
}
return string.Empty;
}
public static void FinalizeSheet(string filePath, TargetTableSheet sheet, int dataRowCount, DateTime asOfDate)
{
FinalizeSheet(filePath, sheet, dataRowCount, asOfDate, new DateTime(1900, 1, 1));
}
public static string PopulateGF1304_081(string filePath, TargetTableSheet sheet, DateTime asOfDate, System.Data.DataTable dataTable)
{
return PopulateGF1301_081(filePath, sheet, asOfDate, dataTable);
}
public static string PopulateGF1403_111(string filePath, TargetTableSheet sheet, DateTime asOfDate, System.Data.DataTable dataTable)
{
logger.Debug("Populating GF1403_111");
Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook theExcelBook = null;
Worksheet theSheet = null;
bool excelOpened = false;
try {
theExcelBook = theExcelApp.Workbooks.Open(filePath);
excelOpened = true;
theSheet = (Worksheet)theExcelBook.Sheets[1];
int rowStartAt = 8;
for (int i = 0; i < dataTable.Rows.Count && i < 10; i++) {
((Range)theSheet.Cells[rowStartAt + i, 3]).Value2 = dataTable.Rows[i]["CustomerName"];
((Range)theSheet.Cells[rowStartAt + i, 4]).Value2 = dataTable.Rows[i]["IdCode"];
((Range)theSheet.Cells[rowStartAt + i, 5]).Value2 = dataTable.Rows[i]["Balance"];
((Range)theSheet.Cells[rowStartAt + i, 6]).Value2 = dataTable.Rows[i]["Balance"];
((Range)theSheet.Cells[rowStartAt + i, 8]).Value2 = dataTable.Rows[i]["ZC"];
((Range)theSheet.Cells[rowStartAt + i, 9]).Value2 = dataTable.Rows[i]["GZ"];
((Range)theSheet.Cells[rowStartAt + i, 10]).Value2 = dataTable.Rows[i]["CJ"];
((Range)theSheet.Cells[rowStartAt + i, 11]).Value2 = dataTable.Rows[i]["KY"];
((Range)theSheet.Cells[rowStartAt + i, 12]).Value2 = dataTable.Rows[i]["SS"];
}
SubstituteReportHeader(theSheet, sheet, asOfDate);
theExcelBook.Save();
logger.Debug("Population done");
}
catch (Exception ex) {
logger.Error(ex);
throw;
}
finally {
if (excelOpened) {
theExcelBook.Close(false, null, null);
}
theExcelApp.Quit();
if (theSheet != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
}
if (theExcelBook != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
GC.Collect();
}
return string.Empty;
}
public static string PopulateGF1101_121(string filePath, TargetTableSheet sheet, DateTime asOfDate, System.Data.DataTable dataTable)
{
logger.Debug("Populating GF1101_121");
Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook theExcelBook = null;
Worksheet theSheet = null;
bool excelOpened = false;
try {
theExcelBook = theExcelApp.Workbooks.Open(filePath);
excelOpened = true;
theSheet = (Worksheet)theExcelBook.Sheets[1];
int excelRow = 10;
for (int i = 0; i < dataTable.Rows.Count; i++) {
((Range)theSheet.Cells[excelRow, 5]).Value2 = dataTable.Rows[i]["ZC"];
((Range)theSheet.Cells[excelRow, 6]).Value2 = dataTable.Rows[i]["GZ"];
((Range)theSheet.Cells[excelRow, 8]).Value2 = dataTable.Rows[i]["CJ"];
((Range)theSheet.Cells[excelRow, 9]).Value2 = dataTable.Rows[i]["KY"];
((Range)theSheet.Cells[excelRow, 10]).Value2 = dataTable.Rows[i]["SS"];
if (excelRow == 29) { // 2.1 - 2.20 end
excelRow = 31;
}
else if (excelRow == 35) { // 2.22买断式转贴现 end
excelRow = 38;
}
else if (excelRow == 38) { // 7. 个人经营性贷款 end
excelRow = 40;
}
else {
excelRow++;
}
}
SubstituteReportHeader(theSheet, sheet, asOfDate);
theExcelBook.Save();
logger.Debug("Population done");
}
catch (Exception ex) {
logger.Error(ex);
throw;
}
finally {
if (excelOpened) {
theExcelBook.Close(false, null, null);
}
theExcelApp.Quit();
if (theSheet != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
}
if (theExcelBook != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
GC.Collect();
}
return string.Empty;
}
public static string PopulateGF1103_121(string filePath, TargetTableSheet sheet, DateTime asOfDate, System.Data.DataTable dataTable)
{
logger.Debug("Populating GF1103_121");
Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook theExcelBook = null;
Worksheet theSheet = null;
bool excelOpened = false;
try {
theExcelBook = theExcelApp.Workbooks.Open(filePath);
excelOpened = true;
theSheet = (Worksheet)theExcelBook.Sheets[1];
int excelRow = 8;
int lastDirectionId = 0;
for (int i = 0; i < 97; i++) {
if ((int)dataTable.Rows[i]["DirectionId"] > 0 && (int)dataTable.Rows[i]["DirectionId"] != lastDirectionId) {
excelRow += 1; // 此行用公式计算, 跳过
lastDirectionId = (int)dataTable.Rows[i]["DirectionId"];
}
((Range)theSheet.Cells[excelRow, 5]).Value2 = dataTable.Rows[i]["ZC"];
((Range)theSheet.Cells[excelRow, 6]).Value2 = dataTable.Rows[i]["GZ"];
((Range)theSheet.Cells[excelRow, 8]).Value2 = dataTable.Rows[i]["CJ"];
((Range)theSheet.Cells[excelRow, 9]).Value2 = dataTable.Rows[i]["KY"];
((Range)theSheet.Cells[excelRow, 10]).Value2 = dataTable.Rows[i]["SS"];
if (excelRow == 123) { // 20.1国际组织 end
excelRow = 126;
}
else {
excelRow++;
}
}
SubstituteReportHeader(theSheet, sheet, asOfDate);
theExcelBook.Save();
logger.Debug("Population done");
}
catch (Exception ex) {
logger.Error(ex);
throw;
}
finally {
if (excelOpened) {
theExcelBook.Close(false, null, null);
}
theExcelApp.Quit();
if (theSheet != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
}
if (theExcelBook != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
GC.Collect();
}
return string.Empty;
}
public static void InitSheet(string filePath, TargetTableSheet sheet)
{
InitSheet(filePath, sheet, null);
}
public static void FinalizeSheet(string filePath, TargetTableSheet sheet, int dataRowCount, DateTime asOfDate, DateTime asOfDate2)
{
int sheetIndex = sheet.Index;
Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();
theExcelApp.DisplayAlerts = false; // Without this line, the template sheet cannot be deleted.
Workbook theExcelBook = null;
Worksheet theSheet = null;
Worksheet theSheetTemplate = null;
bool excelOpened = false;
try {
theExcelBook = theExcelApp.Workbooks.Open(filePath);
excelOpened = true;
theSheet = (Worksheet)theExcelBook.Sheets[sheetIndex];
theSheetTemplate = (Worksheet)theExcelBook.Sheets[sheetIndex + 1];
bool dummyHeader = false;
int dummyHeaderRows = 0;
if (sheet.TableId == (int)XEnum.ReportType.X_FXDKTB_D
|| sheet.TableId == (int)XEnum.ReportType.X_FXDKBH_D
|| sheet.TableId == (int)XEnum.ReportType.X_CSHSX_M && sheet.Index == 3
) {
dummyHeader = true;
dummyHeaderRows = 1;
}
//Remove data rows left by template
Range oRange;
var sampleRows = (sheet.FooterStartRow - 1) - sheet.RowsBeforeHeader - 1; // (sheet.FooterStartRow - 1) is the last sample row if there is
if (sampleRows > 0) {
int removeRowFrom = dummyHeader ? 1 : 2;
int removeRowTo = dummyHeader ? removeRowFrom + sampleRows : removeRowFrom + sampleRows - 1;
oRange = (Range)theSheet.get_Range(string.Format("{0}:{1}", removeRowFrom, removeRowTo));
oRange.Delete();
}
//Header
if (sheet.RowsBeforeHeader > 0) {
theSheetTemplate.Activate();
oRange = (Range)theSheetTemplate.get_Range("1:" + sheet.RowsBeforeHeader.ToString());
oRange.Select();
oRange.Copy();
theSheet.Activate();
oRange = theSheet.get_Range("1:1");
oRange.Select();
oRange.Insert();
}
var columnCount = sheet.Columns.Count;
if (sheet.TableId == (int)XEnum.ReportType.X_CSHSX_M && sheet.Index == 3) {
columnCount = 27;
}
//Totals
int dataRowFrom = sheet.RowsBeforeHeader + 2 - dummyHeaderRows;
int footerRowFrom = dataRowFrom + dataRowCount;
int footerRowTo = footerRowFrom + (sheet.FooterEndRow - sheet.FooterStartRow);
// Copy the footer back
if (sheet.TableId == (int)XEnum.ReportType.X_FXDKTB_D
|| sheet.TableId == (int)XEnum.ReportType.X_ZXQYZJXQ_S
|| sheet.TableId == (int)XEnum.ReportType.X_CSHSX_M && sheet.Index == 3
) {
if (sheet.FooterStartRow > 0) {
theSheetTemplate.Activate();
oRange = (Range)theSheetTemplate.get_Range(string.Format("{0}:{1}", sheet.FooterStartRow, sheet.FooterEndRow));
oRange.Select();
oRange.Copy();
theSheet.Activate();
oRange = theSheet.get_Range(string.Format("{0}:{0}", footerRowFrom));
oRange.Select();
oRange.Insert();
}
if (sheet.TableId == (int)XEnum.ReportType.X_CSHSX_M && sheet.Index == 3) {
oRange = theSheet.get_Range("A7:A7");
oRange.Select();
}
}
if (sheet.TableId == (int)XEnum.ReportType.X_WJFL_M) {
((Range)theSheet.Cells[footerRowFrom, 1]).Value2 = "合计";
((Range)theSheet.Cells[footerRowFrom, 1]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
if (dataRowCount > 0) {
if (sheet.Name.Equals("逾期")) {
((Range)theSheet.Cells[footerRowFrom, 3]).Value2 = string.Format("=SUM(C{0}:C{1})", dataRowFrom, footerRowFrom - 1);
((Range)theSheet.Cells[footerRowFrom, 4]).Value2 = string.Format("=SUM(D{0}:D{1})", dataRowFrom, footerRowFrom - 1);
((Range)theSheet.Cells[footerRowFrom, 6]).Value2 = string.Format("=SUM(F{0}:F{1})", dataRowFrom, footerRowFrom - 1);
}
else {
((Range)theSheet.Cells[footerRowFrom, 3]).Value2 = string.Format("=SUM(C{0}:C{1})", dataRowFrom, footerRowFrom - 1);
((Range)theSheet.Cells[footerRowFrom, 5]).Value2 = string.Format("=SUM(E{0}:E{1})", dataRowFrom, footerRowFrom - 1);
}
}
}
else if (sheet.TableId == (int)XEnum.ReportType.F_HYB_M) {
((Range)theSheet.Cells[footerRowFrom, 1]).Value2 = "合计";
((Range)theSheet.Cells[footerRowFrom, 1]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
if (dataRowCount > 0) {
((Range)theSheet.Cells[footerRowFrom, 6]).Value2 = string.Format("=SUM(F{0}:F{1})", dataRowFrom, footerRowFrom - 1);
}
}
else if (sheet.TableId == (int)XEnum.ReportType.X_FXDKTB_D || sheet.TableId == (int)XEnum.ReportType.X_FXDKBH_D) {
if (dataRowCount > 0) {
for (int i = 2; i <= sheet.Columns.Count; i++) {
if (i == 5 || i == 8 || i == 11 || i == 15) {
((Range)theSheet.Cells[footerRowFrom, i]).Value2 = string.Format("={0}{1}/B{1}", GetColumnCharacters(i - 1), footerRowFrom);
}
else {
((Range)theSheet.Cells[footerRowFrom, i]).Value2 = string.Format("=SUM({0}{1}:{0}{2})", GetColumnCharacters(i), dataRowFrom, footerRowFrom - 1);
}
}
}
}
if (sheet.TableId == (int)XEnum.ReportType.X_WJFL_M || sheet.TableId == (int)XEnum.ReportType.F_HYB_M) {
//绘制数据部分的表格线
int dataRowStartIndex = sheet.RowsBeforeHeader + 1 + 1;
Range dataRange = theSheet.Range[theSheet.Cells[dataRowStartIndex, 1], theSheet.Cells[footerRowFrom, columnCount]];
dataRange.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
dataRange.Font.Size = 10;
}
else if (sheet.TableId == (int)XEnum.ReportType.X_FXDKTB_D) {
int dataRowStartIndex = sheet.RowsBeforeHeader + 1 + 1;
Range dataRange = theSheet.Range[theSheet.Cells[dataRowStartIndex, 1], theSheet.Cells[footerRowFrom, columnCount]];
dataRange.RowHeight = 21;
}
else if (sheet.TableId == (int)XEnum.ReportType.X_FXDKBH_D) {
Range dataRange = theSheet.Range[theSheet.Cells[dataRowFrom, 1], theSheet.Cells[footerRowFrom, columnCount]];
dataRange.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
dataRange.RowHeight = 24;
// amount & numbers
dataRange = theSheet.Range[theSheet.Cells[dataRowFrom, 2], theSheet.Cells[footerRowFrom, columnCount]];
dataRange.HorizontalAlignment = XlHAlign.xlHAlignRight;
dataRange = theSheet.Range[theSheet.Cells[footerRowFrom, 1], theSheet.Cells[footerRowFrom, columnCount]];
dataRange.Interior.Color = System.Drawing.Color.FromArgb(192, 192, 192);
((Range)theSheet.Cells[footerRowFrom, 1]).Value2 = "总计";
((Range)theSheet.Cells[footerRowFrom, 1]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
}
else if (sheet.TableId == (int)XEnum.ReportType.C_DQDKQK_M) {
columnCount = 2;
while (true) {
if (string.IsNullOrEmpty(((Range)theSheet.Cells[sheet.RowsBeforeHeader + 1, columnCount]).Value2)) {
break;
}
else {
columnCount++;
}
}
columnCount--;
int headerStartIndex = sheet.RowsBeforeHeader + 1;
Range dataRange = theSheet.Range[theSheet.Cells[headerStartIndex, 1], theSheet.Cells[headerStartIndex, columnCount]];
dataRange.Interior.Color = System.Drawing.Color.FromArgb(204, 204, 255);
dataRange = theSheet.Range[theSheet.Cells[headerStartIndex, 1], theSheet.Cells[footerRowFrom, columnCount]];
dataRange.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
dataRange.Font.Size = 10;
((Range)theSheet.Cells[footerRowFrom, 2]).Value2 = "合计";
((Range)theSheet.Cells[footerRowFrom, 2]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
if (dataRowCount > 0) {
if (sheet.Name.IndexOf("对公") >= 0) {
((Range)theSheet.Cells[footerRowFrom, 6]).Value2 = string.Format("=SUM(F{0}:F{1})", dataRowFrom, footerRowFrom - 1);
}
else if (sheet.Name.IndexOf("个人") >= 0) {
((Range)theSheet.Cells[footerRowFrom, 8]).Value2 = string.Format("=SUM(H{0}:H{1})", dataRowFrom, footerRowFrom - 1);
}
}
}
else if (sheet.TableId == (int)XEnum.ReportType.C_XZDKMX_D || sheet.TableId == (int)XEnum.ReportType.C_JQDKMX_D) {
columnCount = 1;
while (true) {
if (string.IsNullOrEmpty(((Range)theSheet.Cells[sheet.RowsBeforeHeader + 1, columnCount]).Value2)) {
break;
}
else {
columnCount++;
}
}
columnCount--;
int headerStartIndex = sheet.RowsBeforeHeader + 1;
// Column Header
Range dataRange = theSheet.Range[theSheet.Cells[headerStartIndex, 1], theSheet.Cells[headerStartIndex, columnCount]];
dataRange.Font.Bold = true;
// Data Rows & Footer
dataRange = theSheet.Range[theSheet.Cells[headerStartIndex, 1], theSheet.Cells[footerRowFrom, columnCount]];
dataRange.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
dataRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
dataRange.Font.Size = 10;
// Footer
dataRange = theSheet.Range[theSheet.Cells[footerRowFrom, 1], theSheet.Cells[footerRowFrom, columnCount]];
dataRange.Interior.Color = System.Drawing.Color.FromArgb(192, 192, 192);
((Range)theSheet.Cells[footerRowFrom, 1]).Value2 = "总计";
((Range)theSheet.Cells[footerRowFrom, 1]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
if (sheet.TableId == (int)XEnum.ReportType.C_XZDKMX_D) {
if (dataRowCount > 0) {
((Range)theSheet.Cells[footerRowFrom, 3]).Value2 = string.Format("=SUM(C{0}:C{1})", dataRowFrom, footerRowFrom - 1);
((Range)theSheet.Cells[footerRowFrom, 9]).Value2 = string.Format("=SUM(I{0}:I{1})", dataRowFrom, footerRowFrom - 1);
}
else {
((Range)theSheet.Cells[footerRowFrom, 3]).Value2 = "0.00";
((Range)theSheet.Cells[footerRowFrom, 9]).Value2 = "0.00";
}
}
else if (sheet.TableId == (int)XEnum.ReportType.C_JQDKMX_D) {
if (dataRowCount > 0) {
((Range)theSheet.Cells[footerRowFrom, 3]).Value2 = string.Format("=SUM(C{0}:C{1})", dataRowFrom, footerRowFrom - 1);
((Range)theSheet.Cells[footerRowFrom, 4]).Value2 = string.Format("=SUM(D{0}:D{1})", dataRowFrom, footerRowFrom - 1);
((Range)theSheet.Cells[footerRowFrom, 10]).Value2 = string.Format("=SUM(J{0}:J{1})", dataRowFrom, footerRowFrom - 1);
((Range)theSheet.Cells[footerRowFrom, 11]).Value2 = string.Format("=SUM(K{0}:K{1})", dataRowFrom, footerRowFrom - 1);
}
else {
((Range)theSheet.Cells[footerRowFrom, 3]).Value2 = "0.00";
((Range)theSheet.Cells[footerRowFrom, 4]).Value2 = "0.00";
((Range)theSheet.Cells[footerRowFrom, 10]).Value2 = "0.00";
((Range)theSheet.Cells[footerRowFrom, 11]).Value2 = "0.00";
}
}
}
else if (sheet.TableId == (int)XEnum.ReportType.X_ZXQYZJXQ_S) {
int dataRowStartIndex = sheet.RowsBeforeHeader + 1 + 1;
Range dataRange = theSheet.Range[theSheet.Cells[dataRowStartIndex, 1], theSheet.Cells[footerRowTo, columnCount]];
dataRange.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
dataRange.Font.Size = 10;
var lastYear = asOfDate.Year - 1;
var lastSeason = asOfDate.Month / 3;
if (lastSeason == 0) { // as_of_date = 2016-1-31
lastYear--;
lastSeason = 4;
}
var season = "";
if (lastSeason == 1 || lastSeason == 3) {
season = string.Format("{0}年度第{1}季度", lastYear + 1, lastSeason);
}
else {
season = string.Format("{0}年{1}半年发放余额", lastYear + 1, (lastSeason == 2 ? "上" : "下"));
}
((Range)theSheet.Cells[sheet.RowsBeforeHeader + 1, columnCount - 1]).Value2 = string.Format("{0}年末发放余额", lastYear);
((Range)theSheet.Cells[sheet.RowsBeforeHeader + 1, columnCount]).Value2 = season;
((Range)theSheet.Cells[1, 1]).Select();
}
else if (sheet.TableId == (int)XEnum.ReportType.X_CSHSX_M && sheet.Index == 3) {
//绘制数据部分的表格线
int dataRowStartIndex = sheet.RowsBeforeHeader + 1 + 1;
Range dataRange = theSheet.Range[theSheet.Cells[dataRowStartIndex, 1], theSheet.Cells[footerRowFrom, columnCount]];
dataRange.Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
}
SubstituteReportHeader(theSheet, sheet, asOfDate, asOfDate2);
theSheetTemplate.Delete();
theExcelBook.Save();
}
catch (Exception ex) {
logger.Error(ex);
throw;
}
finally {
if (excelOpened) {
theExcelBook.Close(false, null, null);
}
theExcelApp.Quit();
if (theSheet != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
}
if (theSheetTemplate != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheetTemplate);
}
if (theExcelBook != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
GC.Collect();
}
}
public static string PopulateGF0102_161(string filePath, TargetTableSheet sheet, DateTime asOfDate, decimal total, decimal guanZhu, decimal ciJi, decimal keYi, decimal sunShi, decimal overdue90)
{
logger.Debug("Populating GF0102_161");
Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook theExcelBook = null;
Worksheet theSheet = null;
bool excelOpened = false;
try {
theExcelBook = theExcelApp.Workbooks.Open(filePath);
excelOpened = true;
theSheet = (Worksheet)theExcelBook.Sheets[1];
((Range)theSheet.Cells[6, 3]).Value2 = total;
((Range)theSheet.Cells[9, 3]).Value2 = guanZhu;
((Range)theSheet.Cells[11, 3]).Value2 = ciJi;
((Range)theSheet.Cells[12, 3]).Value2 = keYi;
((Range)theSheet.Cells[13, 3]).Value2 = sunShi;
((Range)theSheet.Cells[15, 3]).Value2 = overdue90;
SubstituteReportHeader(theSheet, sheet, asOfDate);
theExcelBook.Save();
logger.Debug("Population done");
}
catch (Exception ex) {
logger.Error(ex);
throw;
}
finally {
if (excelOpened) {
theExcelBook.Close(false, null, null);
}
theExcelApp.Quit();
if (theSheet != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
}
if (theExcelBook != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
GC.Collect();
}
return string.Empty;
}
public static string PopulateX_BLDKJC_X_1(string filePath, TargetTableSheet sheet, DateTime asOfDate, System.Data.DataTable dataTable)
{
logger.Debug("Populating X_BLDKJC_X_1");
Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook theExcelBook = null;
Worksheet theSheet = null;
bool excelOpened = false;
try {
theExcelBook = theExcelApp.Workbooks.Open(filePath);
excelOpened = true;
theSheet = (Worksheet)theExcelBook.Sheets[sheet.Index];
int excelRow = 7;
for (int i = 0; i < dataTable.Rows.Count; i++) {
for (int j = 0; j < 4; j++) {
if (excelRow >= 24) {
((Range)theSheet.Cells[excelRow, 2 + j]).Value2 = ((decimal)dataTable.Rows[i][2 + j] / 100);
}
else {
((Range)theSheet.Cells[excelRow, 2 + j]).Value2 = dataTable.Rows[i][2 + j];
}
}
if (excelRow == 8) { // 关注类贷款余额 starts
excelRow = 10;
}
else if (excelRow == 11) { // 法人类不良贷款余额 starts
excelRow = 17;
}
else if (excelRow == 19) { // 个人类不良贷款余额 starts
excelRow = 21;
}
else {
excelRow++;
}
}
SubstituteReportHeader(theSheet, sheet, asOfDate);
theExcelBook.Save();
logger.Debug("Population done");
}
catch (Exception ex) {
logger.Error(ex);
throw;
}
finally {
if (excelOpened) {
theExcelBook.Close(false, null, null);
}
theExcelApp.Quit();
if (theSheet != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
}
if (theExcelBook != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
GC.Collect();
}
return string.Empty;
}
public static string PopulateGF1900_151(string filePath, TargetTableSheet sheet, DateTime asOfDate, System.Data.DataTable dataTable)
{
logger.Debug("Populating GF1900_151");
Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook theExcelBook = null;
Worksheet theSheet = null;
bool excelOpened = false;
try {
theExcelBook = theExcelApp.Workbooks.Open(filePath);
excelOpened = true;
theSheet = (Worksheet)theExcelBook.Sheets[1];
int rowStartAt = 7;
for (int i = 0; i < dataTable.Rows.Count; i++) {
for (int j = 0; j < 5; j++) {
((Range)theSheet.Cells[rowStartAt + i, 14 + j]).Value2 = dataTable.Rows[i][2 + j];
}
}
SubstituteReportHeader(theSheet, sheet, asOfDate);
theExcelBook.Save();
logger.Debug("Population done");
}
catch (Exception ex) {
logger.Error(ex);
throw;
}
finally {
if (excelOpened) {
theExcelBook.Close(false, null, null);
}
theExcelApp.Quit();
if (theSheet != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
}
if (theExcelBook != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
GC.Collect();
}
return string.Empty;
}
public static string PopulateSF6401_141(string filePath, TargetTableSheet sheet, DateTime asOfDate, System.Data.DataTable dataTable, System.Data.DataTable dataTable2)
{
logger.Debug("Populating SF6401_141");
Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook theExcelBook = null;
Worksheet theSheet = null;
bool excelOpened = false;
try {
theExcelBook = theExcelApp.Workbooks.Open(filePath);
excelOpened = true;
theSheet = (Worksheet)theExcelBook.Sheets[1];
// 1.1 - 1.21
int rowStartAt = 7;
for (int i = 0; i < 21; i++) {
for (int j = 1; j <= 6; j++) {
((Range)theSheet.Cells[rowStartAt + i, 2 + j]).Value2 = dataTable.Rows[i]["Balance" + j.ToString()];
}
((Range)theSheet.Cells[rowStartAt + i, 9]).Value2 = dataTable.Rows[i]["Balance6"];
}
// 2. 贷款当年累计发放额
for (int j = 1; j <= 6; j++) {
((Range)theSheet.Cells[29, 2 + j]).Value2 = dataTable.Rows[21]["Balance" + j.ToString()];
}
((Range)theSheet.Cells[29, 9]).Value2 = dataTable.Rows[21]["Balance6"];
// 3. 贷款当年累计发放户数
// 4. 贷款当年累计申请户数
for (int j = 1; j <= 6; j++) {
((Range)theSheet.Cells[30, 2 + j]).Value2 = dataTable2.Rows[0]["Count" + j.ToString()];
((Range)theSheet.Cells[31, 2 + j]).Value2 = dataTable2.Rows[0]["Count" + j.ToString()];
}
((Range)theSheet.Cells[30, 9]).Value2 = dataTable2.Rows[0]["Count6"];
((Range)theSheet.Cells[31, 9]).Value2 = dataTable2.Rows[0]["Count6"];
SubstituteReportHeader(theSheet, sheet, asOfDate);
theExcelBook.Save();
logger.Debug("Population done");
}
catch (Exception ex) {
logger.Error(ex);
throw;
}
finally {
if (excelOpened) {
theExcelBook.Close(false, null, null);
}
theExcelApp.Quit();
if (theSheet != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
}
if (theExcelBook != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
GC.Collect();
}
return string.Empty;
}
public static bool SubstituteReportHeader(Worksheet theSheet, TargetTableSheet sheet, DateTime asOfDate, DateTime asOfDate2)
{
var changed = false;
var columnCount = sheet.Columns.Count;
for (int i = 1; i <= sheet.RowsBeforeHeader; i++) {
for (int j = 1; j <= columnCount; j++) {
var cell = ((Range)theSheet.Cells[i, j]);
string val = "";
try {
val = cell.Value2;
}
catch { }
if (!string.IsNullOrWhiteSpace(val)) {
if (asOfDate2.Year > 2001) {
if (val.IndexOf("year2") >= 0) {
val = val.Replace("year2", asOfDate2.Year.ToString());
}
if (val.IndexOf("month2") >= 0) {
val = val.Replace("month2", asOfDate2.Month.ToString());
}
if (val.IndexOf("day2") >= 0) {
val = val.Replace("day2", asOfDate2.Day.ToString());
}
}
if (val.IndexOf("year") >= 0) {
val = val.Replace("year", asOfDate.Year.ToString());
}
if (val.IndexOf("month") >= 0) {
val = val.Replace("month", asOfDate.Month.ToString());
}
if (val.IndexOf("day") >= 0) {
val = val.Replace("day", asOfDate.Day.ToString());
}
if (!val.Equals((string)cell.Value2)) {
cell.Value2 = val;
changed = true;
}
}
}
}
return changed;
}
public static string PopulateGF0107_141(string filePath, TargetTableSheet sheet, DateTime asOfDate, System.Data.DataTable dataTable)
{
logger.Debug("Populating GF0107_141");
Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook theExcelBook = null;
Worksheet theSheet = null;
bool excelOpened = false;
try {
theExcelBook = theExcelApp.Workbooks.Open(filePath);
excelOpened = true;
theSheet = (Worksheet)theExcelBook.Sheets[1];
// 2.1 - 2.20
int rowStartAt = 10;
for (int i = 0; i < 20; i++) {
((Range)theSheet.Cells[rowStartAt + i, 3]).Value2 = dataTable.Rows[i]["Balance"];
}
// 2.21 个人贷款(不含个人经营性贷款)
rowStartAt = 31;
for (int i = 0; i < 4; i++) {
((Range)theSheet.Cells[rowStartAt + i, 3]).Value2 = dataTable.Rows[i + 20]["Balance"];
}
// 4. 个人经营性贷款
((Range)theSheet.Cells[38, 3]).Value2 = dataTable.Rows[24]["Balance"];
SubstituteReportHeader(theSheet, sheet, asOfDate);
theExcelBook.Save();
logger.Debug("Population done");
}
catch (Exception ex) {
logger.Error(ex);
throw;
}
finally {
if (excelOpened) {
theExcelBook.Close(false, null, null);
}
theExcelApp.Quit();
if (theSheet != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
}
if (theExcelBook != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
GC.Collect();
}
return string.Empty;
}
public static void InitSheet(string filePath, TargetTableSheet sheet, List<string> columnNames)
{
if (sheet.RowsBeforeHeader == 0 && sheet.FooterStartRow == 0) {
return;
}
Microsoft.Office.Interop.Excel.Application theExcelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook theExcelBook = null;
Worksheet theSheet = null;
Worksheet theSheetTemplate = null;
bool excelOpened = false;
try {
theExcelBook = theExcelApp.Workbooks.Open(filePath);
excelOpened = true;
theSheet = (Worksheet)theExcelBook.Sheets[sheet.Index];
if (!theSheet.Name.Equals(sheet.Name)) {
theSheet.Name = sheet.Name;
}
theSheet.Copy(Type.Missing, theSheet);
((Worksheet)theExcelBook.Sheets[sheet.Index + 1]).Name = theSheet.Name + "Template";
Range range = null;
// Remove footer
if (sheet.FooterStartRow > 0) {
range = (Range)theSheet.get_Range(string.Format("{0}:{1}", sheet.FooterStartRow, sheet.FooterEndRow));
range.Delete();
}
// Make the column header row as the first row
if (sheet.RowsBeforeHeader > 0) {
range = (Range)theSheet.get_Range("1:" + sheet.RowsBeforeHeader.ToString());
range.Delete();
}
if (columnNames != null) {
for (int i = 1; i <= columnNames.Count; i++) {
theSheet.Cells[1, i] = columnNames[i - 1];
}
}
theExcelBook.Save();
}
finally {
if (excelOpened) {
theExcelBook.Close(false, null, null);
}
theExcelApp.Quit();
if (theSheet != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheet);
}
if (theSheetTemplate != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theSheetTemplate);
}
if (theExcelBook != null) {
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelBook);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(theExcelApp);
GC.Collect();
}
}
