/// <summary>
/// Workbook을 PDF파일 형식으로 저장합니다.
/// </summary>
/// <param name="fileName">파일 이름을 포함한 저장 경로.</param>
public void ExportAsPdf(string fileName)
{
ActiveWorkbook.ExportAsFixedFormat(
XlFixedFormatType.xlTypePDF, // Type
fileName, // Filename
XlFixedFormatQuality.xlQualityStandard, // Quality
Type.Missing, // IncludeDocProperties
Type.Missing, // IgnorePrintAreas
Type.Missing, // From
Type.Missing, // To
false, // OpenAfterPublish
Type.Missing // FixedFormatExtClassPtr
);
}
/// <summary>
/// Đóng Excel file, thoát Excel Application, ngắt kết nối với COM+ và CG thu dọn rác
/// </summary>
public void CloseWorkbook()
{
//http://www.velocityreviews.com/forums/t75190-re-how-to-close-excel-object-with-c.html
ActiveWorksheet = null;
try
{
//Close and Quit Excel:
if (ActiveWorkbook != null)
{
ActiveWorkbook.Close(false, string.Empty, null);
}
}
finally { }
try
{
if (_ExcelApplication != null)
{
_ExcelApplication.Workbooks.Close();
_ExcelApplication.Quit();
}
}
finally { }
try
{
//Close ComObject:
if (ActiveWorkbook != null)
{
Marshal.ReleaseComObject(ActiveWorkbook);
ActiveWorkbook = null;
}
}
finally { }
try
{
if (_ExcelApplication != null)
{
Marshal.ReleaseComObject(_ExcelApplication);
_ExcelApplication = null;
_ExcelProcessId = 0;
}
}
finally { }
GC.Collect(); // force final cleanup!
}
/// <summary>
/// 변경된 모든 사항을 지정된 경로에 지정된 형식으로 저장합니다.
/// </summary>
/// <param name="type">저장할 파일의 형식.</param>
/// <param name="fileName">파일을 저장할 경로.</param>
public void SaveAs(string fileName, XlFileFormat fileFormat)
{
ActiveWorkbook.SaveAs(
fileName, // Filename
fileFormat, // FileFormat
Type.Missing, // Password
Type.Missing, // WriteResPassword
Type.Missing, // ReadOnlyRecommended
Type.Missing, // CreateBackup
XlSaveAsAccessMode.xlNoChange, // AccessMode
Type.Missing, // ConflictResolution
Type.Missing, // AddToMru
Type.Missing, // TextCodepage
Type.Missing, // TextVisualLayout
Type.Missing // Local
);
}
static void Main(string[] args)
{
var excelApp = new Excel.Application();
excelApp.Workbooks.Add();
// VBA code goes here:
excelApp.Columns["A:B"].Select
excelApp.ActiveWorkbook.Worksheets("test").Sort.SortFields.Clear
excelApp.ActiveWorkbook.Worksheets("test").Sort.SortFields.Add Key := Range["A:B"], _
SortOn := Excel.XlSortOn.xlSortOnValues, Order := Excel.XlSortOrder.xlAscending, _
DataOption := Excel.XlSortDataOption.xlSortNormal
excelApp.ActiveWorkbook.Worksheets("test").Sort.SetRange Range["A:B"]
excelApp.ActiveWorkbook.Worksheets("test").Sort.Header = Excel.XlYesNoGuess.xlGuess
excelApp.ActiveWorkbook.Worksheets("test").Sort.MatchCase = False
excelApp.ActiveWorkbook.Worksheets("test").Sort.Orientation = Excel.Constants.xlTopToBottom
excelApp.ActiveWorkbook.Worksheets("test").Sort.SortMethod = Excel.XlSortMethod.xlPinYin
excelApp.ActiveWorkbook.Worksheets("test").Sort.Apply
excelApp.Visible = true;
}
public void ConvertAssessment(AssessmentConverter converter)
{
//verify if sheet exists
try
{
var value = ActiveSheet.Range["A1"].Value2;
}
catch (Exception)
{
System.Windows.Forms.MessageBox.Show("Select assessment first before you start using plugin.", "Format error");
return;
}
LoadAllProfiles();
var techologyGroups = GetTechologyGroups(ProfilesDictionary);
Assessment assessment = Assessment.Build(ActiveSheet, techologyGroups);
if (assessment == null)
{
System.Windows.Forms.MessageBox.Show("Unable to convert assessment, unknown format.", "Format error");
return;
}
EngineerProfile profile = LoadEngineerProfile(assessment);
if (profile == null)
{
System.Windows.Forms.MessageBox.Show("Unable to detect a profile of assessment, ensure please: 1) profile configuration has keywords selected, 2) assessment has keywords.", "Format error");
return;
}
string workbookName = ActiveWorkbook.Name;
var excelRows = converter.Convert(assessment, profile);
if (Workbooks == null)
{
System.Windows.Forms.MessageBox.Show("Plugin requires restart Excel.", "Internal error");
return;
}
var newWorkBook = Workbooks.Add();
var activeSheet = newWorkBook.ActiveSheet as Excel.Worksheet;
//write header
var worker = new ExcelWorker(activeSheet);
//header section
int row = 2;
row = WriteHeader(profile, worker, row);
//2 rows separation
row += 2;
//
worker.SetAValue(row, profile.TechnicalAreaText).SetBold(true).SetColor(Assessment.OleHeaderColor).SetWidth(60).SetHeight(15);
worker.SetValue(row, defaultScaleColumn, profile.ScaleText).SetBold(true).SetColor(Assessment.OleHeaderColor);
row++;
foreach (var item in excelRows)
{
worker.SetAValue(row, item.Technology).SetColor(item.Color).SetBold(item.isBold);
worker.SetValue(row, defaultScaleColumn, item.Scale).SetColor(item.Color).SetBold(item.isBold);
row++;
}
MsoFileDialogType dlgType = MsoFileDialogType.msoFileDialogSaveAs;
Application.FileDialog[dlgType].InitialFileName = string.Format("{0}_{1}", converter.ConverterName, workbookName);
Application.FileDialog[dlgType].Show();
if (Application.FileDialog[dlgType].SelectedItems.Count > 0)
{
ActiveWorkbook.SaveAs(Application.FileDialog[dlgType].SelectedItems.Item(1), Excel.XlFileFormat.xlOpenXMLWorkbook);
}
}
///////////////////////////////////////////////////////////////////////////
//
// METHODS
//
///////////////////////////////////////////////////////////////////////////
#region METHODS
/// <summary>
/// 변경된 모든 사항을 Workbook 파일에 저장합니다.
/// </summary>
public void Save()
{
ActiveWorkbook.Save();
}
