public static void prjTb(System.Data.DataTable fnlCns, System.Data.DataTable nwPrm, string prjNme)
{
string str;
int num;
DataRow row2;
double num2;
int num4;
double num5;
if (prjNme.Length > 0x17)
{
str = prjNme.Substring(0, 0x17);
}
else
{
str = prjNme;
}
List<string> list = new List<string>();
foreach (DataColumn column in fnlCns.Columns)
{
if (((column.ColumnName == "Prior Premium") || (column.ColumnName == "Prior Subsidy")) || (column.ColumnName == "Prior EE Contribution"))
{
list.Add(column.ColumnName);
}
if (column.ColumnName.Contains("Prior") && column.ColumnName.Contains("Surcharge"))
{
list.Add(column.ColumnName);
}
}
System.Data.DataTable table = new System.Data.DataTable();
for (num = 0; num < list.Count; num++)
{
table.Columns.Add(list[num]);
}
foreach (DataRow row in fnlCns.Rows)
{
row2 = table.NewRow();
foreach (DataColumn column in table.Columns)
{
row2[column.ColumnName] = row[column.ColumnName];
}
table.Rows.Add(row2);
}
List<double> list2 = new List<double>();
foreach (DataColumn column in table.Columns)
{
num2 = 0.0;
foreach (DataRow row in table.Rows)
{
num2 += double.Parse(row[column.ColumnName].ToString());
}
list2.Add(num2);
}
List<string> list3 = new List<string>();
foreach (DataColumn column in fnlCns.Columns)
{
if (((column.ColumnName == "New Premium") || (column.ColumnName == "New Subsidy")) || (column.ColumnName == "New EE Contribution"))
{
list3.Add(column.ColumnName);
}
if (column.ColumnName.Contains("New") && column.ColumnName.Contains("Surcharge"))
{
list3.Add(column.ColumnName);
}
}
System.Data.DataTable table2 = new System.Data.DataTable();
for (num = 0; num < list3.Count; num++)
{
table2.Columns.Add(list3[num]);
}
foreach (DataRow row in fnlCns.Rows)
{
row2 = table2.NewRow();
foreach (DataColumn column in table2.Columns)
{
row2[column.ColumnName] = row[column.ColumnName];
}
table2.Rows.Add(row2);
}
List<double> list4 = new List<double>();
foreach (DataColumn column in table2.Columns)
{
num2 = 0.0;
foreach (DataRow row in table2.Rows)
{
num2 += double.Parse(row[column.ColumnName].ToString());
}
list4.Add(num2);
}
bool flag = false;
foreach (Worksheet worksheet in oWB.Worksheets)
{
if (worksheet.Name.Equals(str + " Summary"))
{
flag = true;
}
}
if (flag)
{
throw new Exception("Projection with same name already exists");
}
oSheet = (Worksheet) shts.Add();
oSheet.Name = str + " Summary";
oSheet.Activate();
int count = 0;
count = oWB.Sheets.Count;
oSheet.Move(null, shts[count]);
NetOffice.ExcelApi.Range entireColumn = null;
NetOffice.ExcelApi.Range range2 = null;
for (num = 0; num < table.Columns.Count; num++)
{
oSheet.Cells[5, num + 1].Value = table.Columns[num].ColumnName;
oSheet.Cells[5, num + 1].Interior.Color = Color.FromArgb(0xc5d9f1);
oSheet.Cells[6, num + 1].Value = list2[num];
oSheet.Cells[6, num + 1].HorizontalAlignment = HorizontalAlignment.Center;
oSheet.Cells[6, num + 1].NumberFormat = "$ #,###,###.00";
range2 = oSheet.Cells[6, num + 1];
entireColumn = range2.EntireColumn;
entireColumn.AutoFit();
}
for (num = 0; num < table2.Columns.Count; num++)
{
oSheet.Cells[8, num + 1].Value = table2.Columns[num].ColumnName;
oSheet.Cells[8, num + 1].Interior.Color = Color.FromArgb(0xc5d9f1);
oSheet.Cells[9, num + 1].Value = list4[num];
oSheet.Cells[9, num + 1].HorizontalAlignment = HorizontalAlignment.Center;
oSheet.Cells[9, num + 1].NumberFormat = "$ #,###,###.00";
range2 = oSheet.Cells[6, num + 1];
entireColumn = range2.EntireColumn;
entireColumn.AutoFit();
}
oSheet.Cells[1, 1].Value = prjNme + " Summary";
oSheet.Cells[1, 1].Font.Bold = true;
oSheet.Cells[1, 1].Font.Size = 20;
oSheet = (Worksheet) shts.Add();
oSheet.Activate();
count = oWB.Sheets.Count;
oSheet.Move(null, shts[count]);
oSheet.Name = str + " Rates";
List<int> list5 = new List<int>();
for (num = 0; num < nwPrm.Columns.Count; num++)
{
oSheet.Cells[4, num + 1].Value = nwPrm.Columns[num].ColumnName;
oSheet.Cells[4, num + 1].Interior.Color = Color.FromArgb(0xc5d9f1);
if ((nwPrm.Columns[num].ColumnName == "AV") || (nwPrm.Columns[num].ColumnName == "Factor"))
{
list5.Add(num);
}
}
object[,] objArray = new object[nwPrm.Rows.Count, nwPrm.Columns.Count];
for (num = 0; num < nwPrm.Rows.Count; num++)
{
num4 = 0;
while (num4 < nwPrm.Columns.Count)
{
try
{
objArray[num, num4] = double.Parse(nwPrm.Rows[num][num4].ToString());
}
catch
{
objArray[num, num4] = nwPrm.Rows[num][num4];
}
num4++;
}
}
NetOffice.ExcelApi.Range range3 = oSheet.Cells[5, 1];
NetOffice.ExcelApi.Range range4 = oSheet.Cells[nwPrm.Rows.Count + 4, nwPrm.Columns.Count];
oSheet.Range(range3, range4).Value = objArray;
for (num = 0; num < nwPrm.Columns.Count; num++)
{
range2 = oSheet.Cells[4, num + 1];
try
{
num5 = double.Parse(oSheet.Cells[5, num + 1].Value.ToString());
if ((oSheet.Cells[4, num + 1].Value.ToString() != "AV") && (oSheet.Cells[4, num + 1].Value.ToString() != "Factor"))
{
entireColumn = range2.EntireColumn;
entireColumn.NumberFormat = "$ #,###.00";
}
entireColumn.HorizontalAlignment = HorizontalAlignment.Center;
entireColumn.AutoFit();
}
catch
{
entireColumn = range2.EntireColumn;
entireColumn.HorizontalAlignment = HorizontalAlignment.Center;
entireColumn.AutoFit();
}
}
oSheet.Cells[1, 1].Value = prjNme + " New Rates and Contributions";
oSheet.Cells[1, 1].Font.Bold = true;
oSheet.Cells[1, 1].Font.Size = 20;
oSheet = (Worksheet) shts.Add();
oSheet.Activate();
count = oWB.Sheets.Count;
oSheet.Move(null, shts[count]);
oSheet.Name = str + " Detail";
for (num = 0; num < fnlCns.Columns.Count; num++)
{
oSheet.Cells[4, num + 1].Value = fnlCns.Columns[num].ColumnName;
oSheet.Cells[4, num + 1].Interior.Color = Color.FromArgb(0xc5d9f1);
}
objArray = new object[fnlCns.Rows.Count, fnlCns.Columns.Count];
for (num = 0; num < fnlCns.Rows.Count; num++)
{
for (num4 = 0; num4 < fnlCns.Columns.Count; num4++)
{
try
{
objArray[num, num4] = double.Parse(fnlCns.Rows[num][num4].ToString());
}
catch
{
objArray[num, num4] = fnlCns.Rows[num][num4];
}
}
}
range3 = oSheet.Cells[5, 1];
range4 = oSheet.Cells[fnlCns.Rows.Count + 4, fnlCns.Columns.Count];
oSheet.Range(range3, range4).Value = objArray;
for (num = 0; num < fnlCns.Columns.Count; num++)
{
range2 = oSheet.Cells[4, num + 1];
try
{
num5 = double.Parse(oSheet.Cells[5, num + 1].Value.ToString());
entireColumn = range2.EntireColumn;
if (num > 1)
{
entireColumn.NumberFormat = "$ #,###.00";
}
entireColumn.HorizontalAlignment = HorizontalAlignment.Center;
entireColumn.AutoFit();
}
catch
{
entireColumn = range2.EntireColumn;
entireColumn.HorizontalAlignment = HorizontalAlignment.Center;
entireColumn.AutoFit();
}
}
oSheet.Cells[1, 1].Value = prjNme + " Detailed Census and Financial Information";
oSheet.Cells[1, 1].Font.Bold = true;
oSheet.Cells[1, 1].Font.Size = 20;
oSheet = (Worksheet) shts[str + " Summary"];
oSheet.Select();
}
/// <summary>
/// ExcelファイルのA1セル選択処理
/// 引数１：Excelファイルパス
/// </summary>
/// <param name="args"></param>
/// <returns></returns>
public static int Main(string[] args)
{
//引数チェック
if (!CheckArgs(args))
{
return(NG);
}
//Excel起動
WriteLog("Excel起動待ち");
using (Excel.Application excelApplication = new Excel.Application())
{
WriteLog("Excel起動");
try
{
//Excel描画停止（速度改善のため）
ExcelBeginUpdate(excelApplication);
//対象ファイルを開く
WriteLog("ファイル開く");
Excel.Workbook workBook = excelApplication.Workbooks.Open(filePath);
//A1セルを選択状態にする
WriteLog($"全シート数：{workBook.Sheets.Count}");
for (int i = 1; i <= workBook.Sheets.Count; i++)
{
WriteLog($"{i}シート目処理中");
//シート取得
Excel.Worksheet sheet = (Excel.Worksheet)workBook.Sheets[i];
WriteLog($"シート名：{sheet.Name}");
//非表示のシートは操作に失敗するので無視する
if (sheet.Visible != Excel.Enums.XlSheetVisibility.xlSheetVisible)
{
continue;
}
//シートを選択状態にする（こうしないとセル選択に失敗する）
sheet.Select();
//倍率を100%に変更
excelApplication.ActiveWindow.Zoom = 100;
//スクロールを一番先頭に変更（選択セルを変更しても、スクロールバーの位置が元のままなので、先頭に移動）
excelApplication.ActiveWindow.ScrollColumn = 1;
excelApplication.ActiveWindow.ScrollRow = 1;
//一番左上のA1セルを選択状態に変更
sheet.Range("A1").Select();
}
//一番左に存在するシートを選択状態にする
WriteLog("一番左のシートを選択");
Excel.Worksheet firstSheet = (Excel.Worksheet)workBook.Sheets[1];
firstSheet.Select();
//保存
WriteLog("保存");
workBook.Save();
workBook.Close();
//Excel終了
excelApplication.Quit();
}
catch (Exception ex)
{
//何らかのエラー発生
WriteLog("例外エラー発生", ex);
return(NG);
}
finally
{
//Excelを閉じる
try
{
excelApplication.Quit();
excelApplication.Dispose();
}
catch { } //例外発生時は無視
}
}
return(OK);
}