using System; using Excel; using FarPoint.Excel; using Microsoft.Office; namespace LossExcel { /// <summary> /// 执行宏 /// </summary> public class ExcelMacroHelper { public ExcelMacroHelper() { } /// <param name="excelFilePath">excel文件名</param> /// <param name="macroName">Macro名</param> /// <param name="rtnValue"></param> public void RunExcelMacro(string excelFilePath,string macroName,out object rtnValue) { object oMissing = System.Reflection.Missing.Value; object[] parasObjects; parasObjects = new object[1]; parasObjects[0] = macroName; Excel.ApplicationClass excel = new Excel.ApplicationClass(); Excel.Workbooks oBooks = excel.Workbooks; Excel._Workbook oBook = null; oBook = oBooks.Open(excelFilePath ,oMissing ,oMissing ,oMissing ,oMissing ,oMissing ,oMissing ,oMissing ,oMissing ,oMissing ,oMissing ,oMissing ,oMissing ,oMissing ,oMissing ); //執行マクロ rtnValue = this.RunMacro(excel,parasObjects); oBook.Save();//保存 oBook.Close(false,oMissing,oMissing);//退出workbook #region 釈放対象 //釈放workbook System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook); oBook = null; //釈放workbooks System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks); oBooks = null; //关闭excel excel.Quit(); //釈放excel System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); excel = null; //回収 GC.Collect(); #endregion } /// <summary> /// 執行宏 /// </summary> /// <param name="oApp"></param> /// <param name="oRunArgs"></param> /// <returns></returns> private object RunMacro(object oApp,object[] oRunArgs) { try { object objrtn; objrtn = oApp.GetType().InvokeMember( "Run" ,System.Reflection.BindingFlags.Default|System.Reflection.BindingFlags.InvokeMethod ,null ,oApp ,oRunArgs); return objrtn; } catch(Exception ex) { if (ex.InnerException.Message.ToString().Length > 0) { throw ex.InnerException; } else { throw ex; } } } } }
using
System;
using
System.Drawing;
using
System.Collections;
using
System.ComponentModel;
using
System.Windows.Forms;
using
System.Data;
using
TRE.Shared.DBProcessException;
using
TRE.Shared;
using
System.Text;
using
System.Data.SqlClient;
using
System.IO;
using
Excel;
using
FarPoint.Excel;
using
Microsoft.Office;
using
LossExcel;
namespace
LossExcel
{
///
<summary>
///
Form1
///
</summary>
public
class
Form1 : System.Windows.Forms.Form
{
private
FarPoint.Win.Spread.FpSpread sprData;
private
FarPoint.Win.Spread.SheetView sprData_Sheet1;
private
System.Windows.Forms.Button btnExcel;
private
DBProcess Mps;
private
System.Data.DataTable dtMakePlan
=
new
System.Data.DataTable();
private
string
macroName
=
"
MakePlan
"
;
private
string
NewFileName;
private
string
BranchName;
private
string
DepartmentName;
private
System.ComponentModel.Container components
=
null
;
public
Form1()
{
//
//
Windows
//
InitializeComponent();
this
.Mps
=
new
DBProcess(
"
MPS_DB
"
);
}
protected
override
void
Dispose(
bool
disposing )
{
if
( disposing )
{
if
(components
!=
null
)
{
components.Dispose();
}
}
base
.Dispose( disposing );
}
#region
Windows 系统自动生成的代码
private
void
InitializeComponent()
{
//
略
}
#endregion
[STAThread]
static
void
Main()
{
System.Windows.Forms.Application.Run(
new
Form1());
}
private
void
Form1_Load(
object
sender, System.EventArgs e)
{
this
.dtMakePlan
=
this
.GetMakePlanInfo().Tables[
0
];
this
.sprData.DataSource
=
this
.dtMakePlan;
}
///
<summary>
///
///
</summary>
///
<returns></returns>
private
DataSet GetMakePlanInfo()
{
DataSet dsMakePlan
=
new
DataSet();
string
strSQL
=
"
SELECT MakePlanDay,SetItems,AllItems,DuiBi/100,LossPercent/100,MakePercent/100 FROM dbo.TD_MakePlan
"
;
try
{
this
.Mps.Open();
dsMakePlan
=
this
.Mps.OpenDataSet(strSQL);
return
dsMakePlan;
}
catch
{
return
null
;
}
finally
{
this
.Mps.Close();
}
}
///
<summary>
///
savedate
///
</summary>
private
void
WriteExcel()
{
FileInfo OldFileName
=
new
FileInfo(
@"
c:/作成指示/123.xls
"
);
if
(
!
OldFileName.Exists)
{
MessageBox.Show(
"
模版不存在
"
,
"
作成指示系统
"
,MessageBoxButtons.OK,MessageBoxIcon.Information);
return
;
}
SaveFileDialog mySave
=
new
SaveFileDialog();
mySave.Filter
=
"
excel files(*.xls)|*.xls
"
;
mySave.FileName
=
@"
指示率图(
"
+
this
.BranchName
+
"
)
"
+
DateTime.Now.Year.ToString()
+
DateTime.Now.Month.ToString()
+
DateTime.Now.Day.ToString()
+
DateTime.Now.Hour.ToString()
+
DateTime.Now.Minute.ToString()
+
DateTime.Now.Second.ToString();
if
(mySave.ShowDialog()
!=
DialogResult.OK)
{
return
;
}
else
{
NewFileName
=
mySave.FileName;
try
{
OldFileName.CopyTo(NewFileName,
true
); }
catch
(Exception ee)
{
MessageBox.Show(ee.Message);
return
;
}
}
Excel.Application excel
=
new
Excel.ApplicationClass();
object
missing
=
System.Reflection.Missing.Value;
Excel.Workbooks workbooks
=
excel.Workbooks;
workbooks.Open(NewFileName
,missing,missing,missing,missing
,missing,missing,missing,missing
,missing,missing,missing,missing
,missing,missing);
Excel.Sheets sheets
=
excel.Sheets;
Excel._Worksheet sheet;
sheet
=
(Excel._Worksheet)sheets.get_Item(
1
);
//
-------2009/02/18
sheet.Cells[
1
,
1
]
=
this
.dtMakePlan.Rows.Count;
sheet.Cells[
2
,
2
]
=
this
.BranchName;
sheet.Cells[
3
,
2
]
=
this
.DepartmentName;
//
-------
for
(
int
i
=
0
;i
<
this
.dtMakePlan.Rows.Count;i
++
)
{
sheet.Cells[
6
+
i,
1
]
=
this
.dtMakePlan.Rows[i][
0
].ToString().Substring(
0
,
10
)
+
this
.DateToWeek(
this
.dtMakePlan.Rows[i][
0
].ToString());
for
(
int
j
=
1
;j
<
6
;j
++
)
{
//
sheet.Cells[6+i,1+j] = dtMakePlan.Rows[i][j];
sheet.Cells[
6
+
i,
1
+
j]
=
dtMakePlan.Rows[i][j];
}
}
excel.DisplayAlerts
=
false
;
sheet.SaveAs(NewFileName,missing,missing,missing,missing,missing,missing,missing,missing,missing);
excel.Workbooks.Close();
#region
釈放対象
//
关闭excel
excel.Quit();
//
釈放cxcel
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel
=
null
;
//
回収
GC.Collect();
#endregion
}
#region
class
#endregion
///
<summary>
///
///
</summary>
///
<param name="sender"></param>
///
<param name="e"></param>
private
void
btnExcel_Click(
object
sender, System.EventArgs e)
{
this
.BranchName
=
"
aaa
"
;
this
.DepartmentName
=
"
bbb
"
;
this
.WriteExcel();
//
保存数据
object
objRtn
=
new
object
();
ExcelMacroHelper excelMacroHelper
=
new
ExcelMacroHelper();
excelMacroHelper.RunExcelMacro(NewFileName,macroName,
out
objRtn);
}
}
}