读取模板生成Excel文件

/// <summary>
/// 读取Excel模板生成Excel文件
/// </summary>
/// <param name="tempFile">模板文件</param>
/// <param name="exFileName">保存文件名</param>
/// <param name="dtExportData">数据</param>
internal static void ExportBusMonthExcelFile(string tempFile, string exFileName, DataTable dtExportData)
{
if (dtExportData.Rows.Count > 0)
{

// Excelアプリケーションを生成
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
// Excelアプリケーションを非表示に設定
app.Visible = false;
// Excel保存時の確認ダイアログを非表示に設定
app.DisplayAlerts = false;
// Excel使用フラグを設定
app.UserControl = true;
// Excel全てのブックを取得
Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks;
// Excelブックの作成
Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(tempFile);
// Excelシートを取得
Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Sheets;
// 最初のワークシートを選択
Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);
// 最初ワークシートフォーマット定義
worksheet.Cells.NumberFormat = "@";

try
{
int rowno = 2;
for (int i = 0; i < dtExportData.Rows.Count; i++)
{
rowno = rowno + i;
if (!string.IsNullOrEmpty(dtExportData.Rows[i]["NAME"].ToString()))
worksheet.Cells[rowno, "A"] = dtExportData.Rows[i]["NAME"].ToString();
if (!string.IsNullOrEmpty(dtExportData.Rows[i]["SEX"].ToString()))
worksheet.Cells[rowno, "B"] = dtExportData.Rows[i]["SEX"].ToString();
if (!string.IsNullOrEmpty(dtExportData.Rows[i]["ADDRES"].ToString()))
worksheet.Cells[rowno, "C"] = dtExportData.Rows[i]["ADDRES"].ToString();
}

if (File.Exists(exFileName))
File.Delete(exFileName);
workbook.SaveAs(exFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
}
catch (Exception ex)
{
// 写错误日志
DLLogManager.WriteErrLog(MethodBase.GetCurrentMethod().Name, ex.Message);
throw ex;
}

finally
{
// Excelオブジェクトを解放
NAR(EXE_NAME, worksheet);
NAR(EXE_NAME, sheets);
NAR(EXE_NAME, workbook);
NAR(EXE_NAME, workbooks);
app.Quit();
NAR(EXE_NAME, app);
}

}
}

 

public static void NAR(String EXE_NAME, object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
}
catch (Exception ex)
{
// 写错误日志
DLLogManager.WriteErrLog(MethodBase.GetCurrentMethod().Name, ex.Message);
}
finally
{
obj = null;
}
}