/// <summary>
/// Teste si le fichier existe, si oui on l'ouvre sinon on le crée et l'enregistre
/// </summary>
/// <returns></returns>
public bool CreerOuOuvrirFichier()
{
try {
if (File.Exists(this.CheminExcel + "/Programmation_" + this.idProgrammation))
{
_LesWorkBooks = _Application.Workbooks;
_MonClasseur = _LesWorkBooks.Open(this.CheminExcel + "/Programmation_" + this.idProgrammation);
_MaFeuille = (_Worksheet)_Application.ActiveSheet;
return(true);
}
else
{
//Récupère le WorkBook
_MonClasseur = _Application.Workbooks.Add();
//Récupère la feuille Active
_MaFeuille = (_Worksheet)_MonClasseur.ActiveSheet;
this.InscritEntete();
_MonClasseur.SaveAs(this.CheminExcel + "/Programmation_" + this.idProgrammation);
return(true);
}
}
catch (Exception e)
{
this.Logger.Error("Impossible d'ouvrir ou de créer le fichier Excel.");
FermerExcel();
return(false);
}
}
// 打开Excel文件
public void OpenExcelFile()
{
OpenFileDialog opd = new OpenFileDialog();
if (opd.ShowDialog() == DialogResult.OK)
excelOpenFileName = opd.FileName;
textBox4.Text = System.IO.Path.GetFileNameWithoutExtension(excelOpenFileName);
if (excelOpenFileName !="")
{
try
{
excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelWorkBooks = excelApplication.Workbooks;
excelWorkBook = ((Workbook)excelWorkBooks.Open(excelOpenFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
excelWorkSheet = (Worksheet)excelWorkBook.Worksheets[ActiveSheetIndex];
excelApplication.Visible = false;
rangeOfExcel = excelWorkSheet.UsedRange.Cells.Rows.Count;//获取EXCEL行数
stateOfSave = false;
}
catch (Exception e)
{
closeApplication();
MessageBox.Show("(1)没有安装Excel；(2)或没有安装.NET 可编程性支持；\n详细信息："
+ e.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
else
{
MessageBox.Show("未选择文件！");
closeApplication();
}
}
public void SaveAndClose(string path)
{
if (workbook != null)
{
workbook.SaveAs(path);
workbook.Close();
Marshal.FinalReleaseComObject(workbook);
workbook = null;
workbooks.Close();
Marshal.FinalReleaseComObject(workbooks);
workbooks = null;
}
if (excel != null)
{
excel.Application.Workbooks.Close();
excel.Application.Quit();
excel.Quit();
Marshal.FinalReleaseComObject(excel.Application);
Marshal.FinalReleaseComObject(excel);
excel = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
/// <summary>
/// 关闭excel
/// </summary>
public void Close()
{
#region 释放Excel资源
//foreach (_Worksheet sheet in m_objBook.Sheets)
//{
// System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
//}
if (m_objBooks != null && m_objExcel != null)
{
foreach (_Workbook book in m_objBooks)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
}
//System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheets);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objRange);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objFont);
//System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBorders);
m_objExcel = null;
m_objBooks = null;
m_objBook = null;
GC.Collect();
#endregion
}
public static void RunVBATest()
{
//System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
//System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
//object oMissing = System.Reflection.Missing.Value;
Application oExcel = new Application();
oExcel.Visible = true;
Workbooks oBooks = oExcel.Workbooks;
_Workbook oBook = null;
oBook = oBooks.Open("C:\\temp\\Book1.xlsm");
// Run the macro.
RunMacro(oExcel, new Object[] { "TestMsg" });
// Quit Excel and clean up.
oBook.Saved = true;
oBook.Close(false);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
oBook = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
oBooks = null;
oExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);
oExcel = null;
//System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
}
//导出采购订单请款
public static void ExportDDQKDataItem(System.Data.DataTable objdt)
{
System.Data.DataTable dt = objdt;
Application m_xlApp = new Application();
Workbooks workbooks = m_xlApp.Workbooks;
Workbook workbook;
Worksheet wksheet;
workbook = m_xlApp.Workbooks.Open(System.Web.HttpContext.Current.Server.MapPath("采购订单请款模板") + ".xls", Type.Missing, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
m_xlApp.Visible = false; // Excel不显示
m_xlApp.DisplayAlerts = false; // 关闭提示，采用默认的方案执行（合并单元格的时候，如果两个单元格都有数据，会出现一个确认提示）
wksheet = (Worksheet)workbook.Sheets.get_Item(1);
//用数组填充表格
int rowCount = dt.Rows.Count;
int colCount = dt.Columns.Count;
object[,] dataArray = new object[rowCount, colCount];
for (int i = 0; i < rowCount; i++)
{
for (int j = 0; j < colCount; j++)
{
dataArray[i, j] = dt.Rows[i][j];
}
}
m_xlApp.get_Range("A2", m_xlApp.Cells[rowCount + 1, colCount]).Value2 = dataArray;
m_xlApp.get_Range("A2", m_xlApp.Cells[rowCount + 1, colCount]).HorizontalAlignment = XlHAlign.xlHAlignLeft;
wksheet.Columns.EntireColumn.AutoFit();//列宽自适应
string filename = System.Web.HttpContext.Current.Server.MapPath("/Contract_Data/ExportFile/" + "采购订单请款信息" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls");
ExportExcel_Exit(filename, workbook, m_xlApp, wksheet);
}
protected override void HandleWriteStarted()
{
// There are problems with exporting to excel on non-English machines
// See: http://www.made4dotnet.com/Default.aspx?tabid=141&aid=15
iOriginalCulture = System.Threading.Thread.CurrentThread.CurrentCulture;
ChangeToEnglishCulture();
iExcelApp = new Microsoft.Office.Interop.Excel.Application();
iExcelApp.DisplayAlerts = false;
Workbooks workbooks = iExcelApp.Workbooks;
workbooks.Add(XlWBATemplate.xlWBATWorksheet);
iWorkbook = workbooks.get_Item(workbooks.Count);
// Create summary sheet
Sheets sheets = iWorkbook.Worksheets;
iSummary = (Worksheet)sheets.get_Item(1);
CreateSummary(iSummary);
// Create pages
iPages.Add(new Pages.PageUnchanged(iEngine.ResultsUnchanged, iWorkbook));
iPages.Add(new Pages.PageUnique(iEngine.Reconstructor2, iEngine.ResultsUniqueInHeap2, iWorkbook, "Unique in Heap 2", 2, 1));
iPages.Add(new Pages.PageUnique(iEngine.Reconstructor1, iEngine.ResultsUniqueInHeap1, iWorkbook, "Unique in Heap 1", 1, 2));
iPages.Add(new Pages.PageSimpleListing(iEngine.ResultsUnchangedLengthButDifferentContents, iWorkbook, "Same length, different contents"));
iPages.Add(new Pages.PageSimpleListing(iEngine.ResultsChanged, iWorkbook, "Changed", false));
}
/// <summary>
/// 创建Book、sheet
/// </summary>
public void CreateExceFile()
{
UserControl(false);
m_objBooks = (Workbooks)m_objExcel.Workbooks;
m_objBook = (Workbook)(m_objBooks.Add(miss));
sheet = (Worksheet)m_objBook.ActiveSheet;
}
public Excel(string path, int Sheet)
{
if (String.IsNullOrWhiteSpace(path))
{
excel = new _Excel.Application();
wbs = excel.Workbooks;
wb = null;
ws = null;
wb = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
// ExcelWorkBook.Worksheets.Add(); //Adding New Sheet in Excel Workbook
ws = wb.Worksheets[1]; // Compulsory Line in which sheet you want to write data
wb.Worksheets[1].Name = "Sheet"; //Renaming the Sheet1 to Sheet
if (File.Exists(AppDomain.CurrentDomain.BaseDirectory + @"template.xlsx"))
{
File.Delete(AppDomain.CurrentDomain.BaseDirectory + @"template.xlsx");
wb.SaveAs(AppDomain.CurrentDomain.BaseDirectory + @"template.xlsx");
}
else
{
wb.SaveAs(AppDomain.CurrentDomain.BaseDirectory + @"template.xlsx");
}
}
else
{
//this.path = path;
wbs = excel.Workbooks;
wb = wbs.Open(path);
ws = wb.Worksheets[Sheet];
}
}
/// <summary>
/// 默认构造函数
/// </summary>
public Excel()
{
books = app.Workbooks;
book = books.Add();
sheets = book.Worksheets;
sheet = (_Worksheet)sheets.get_Item(1);
}
//当界面加载时，实例化两个对象
private void onFormLoaded(object sender, RoutedEventArgs e)
{
//打开Excel
m_Excel = new Microsoft.Office.Interop.Excel.Application();
m_Workbooks = m_Excel.Application.Workbooks;
//initDataBase();
}
public void Dispose()
{
// Cleanup
excel.DisplayAlerts = false;
wb.Close(SaveChanges: false);
wbs.Close();
excel.Quit();
// Manual disposal because of COM
while (Marshal.ReleaseComObject(excel) != 0)
{
}
while (Marshal.ReleaseComObject(wbs) != 0)
{
}
while (Marshal.ReleaseComObject(wb) != 0)
{
}
while (Marshal.ReleaseComObject(ws) != 0)
{
}
excel = null;
wbs = null;
wb = null;
wbs = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
/// <summary>
///
/// </summary>
public MSExcelUtil()
{
_excelApp = new Application();
_objWorkbooks = _excelApp.Workbooks;
_excelApp.Visible = false;
_excelApp.DisplayAlerts = false;
}
/// <summary>
/// 打开excel
/// </summary>
protected void Create()
{
m_objExcel = new Application();
m_objExcel.Application.DisplayAlerts = false;
m_objBooks = (Workbooks)m_objExcel.Workbooks;
m_objBook = (_Workbook)(m_objBooks.Add(m_objOpt));
}
public void InformeGeneralObras()
{
oExcel = new Application();
oBooks = oExcel.Workbooks;
oBook = oBooks.Add(1);
oSheets = (Sheets)oBook.Worksheets;
oSheet = oSheets.get_Item(1);
this.oSheet.Cells[1,1] = "Consecutivo";
this.oSheet.Cells[1,2] = "Título";
this.oSheet.Cells[1,3] = "Núm. de Material";
this.oSheet.Cells[1,4] = "Año";
this.oSheet.Cells[1,5] = "Tiraje";
int ind = 2;
for (int j = 0; j < obrasImprimir.Count; j++)
{
oSheet.Cells[1][ind] = obrasImprimir[j].Consecutivo;
oSheet.Cells[2][ind] = obrasImprimir[j].Titulo;
oSheet.Cells[3][ind] = obrasImprimir[j].NumMaterial;
oSheet.Cells[4][ind] = obrasImprimir[j].AnioPublicacion;
oSheet.Cells[5][ind] = obrasImprimir[j].Tiraje;
ind++;
}
this.oExcel.ActiveWorkbook.Save();
this.oExcel.Quit();
}
public static void Generate(string source, string destination)
{
Application xlApp = new Application();
xlApp.Visible = false;
xlApp.UserControl = false;
Workbooks xlWorkbooks = xlApp.Workbooks;
Workbook xlWorkbook = xlWorkbooks.Open(source);
xlWorkbook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, destination);
xlWorkbook.Close(false);
xlWorkbooks.Close();
xlApp.Quit(); //MainWindowTitle will become empty afer being close
Marshal.FinalReleaseComObject(xlWorkbook);
Marshal.FinalReleaseComObject(xlWorkbooks);
Marshal.FinalReleaseComObject(xlApp);
GC.Collect();
GC.WaitForPendingFinalizers();
ExcelKiller.Kill(new Log.ServerLogger());
}
public static bool SaveExcel(string savePath, System.Data.DataTable tripTable, System.Data.DataTable testTable)
{
try
{
Application excelApp = new Application();
Workbooks excelBooks = excelApp.Workbooks;
Workbook excelBook = excelBooks.Add(Missing.Value);
Sheets sheets = excelBook.Sheets;
Worksheet tripSheet = ((Worksheet)sheets.get_Item(1));
Worksheet testSheet = ((Worksheet)sheets.get_Item(2));
fillData(ref tripSheet, "Trip", tripTable);
fillData(ref testSheet, "Test", testTable);
excelBook.SaveAs(savePath, 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);
excelBook.Close(Missing.Value, Missing.Value, Missing.Value);
excelBooks.Close();
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;
return(true);
}
catch (Exception exp)
{
MsgBox.Show(exp.Message);
return(false);
}
}
public static int CreateFromRealmsCSV(string source, string destination)
{
var app = CreateExcelApp(destination);
app.ScreenUpdating = false;
Workbooks workbooks = app.Workbooks;
Workbook workbook = workbooks.Add();
Worksheet worksheet = (Worksheet)workbook.Worksheets.Item[1];
CreateWorksheetTemplate(worksheet);
var csv = ConfigureCSV(source);
// Reading records the old way
var departmentRoot = ReadRealmsRecords(csv);
// Writing records the old way
PopulateTemplate(worksheet, departmentRoot);
workbook.SaveAs(destination);
// Cleanup
app.ScreenUpdating = true;
Cleanup(app, workbooks, workbook, worksheet);
return(0);
}
private void close()
{
m_app.DisplayAlerts = false;
m_app.AlertBeforeOverwriting = false;
m_app.Quit();
IntPtr t = new IntPtr(m_app.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
m_app = null;
m_book = null; m_books = null;
ReleaseObj(m_book);
ReleaseObj(m_books);
ReleaseObj(m_sheet);
ReleaseObj(m_sheets);
ReleaseObj(m_app);
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
}
/// <summary>
/// 格式化未供完表格
/// </summary>
/// <param name="file"></param>
public static void FormatWGWExcel(string file)
{
Application app = new Application();
app.DisplayAlerts = false;
app.Visible = false;
app.UserControl = true;
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(file);
Sheets sheets = workbook.Sheets;
Worksheet worksheet = (Worksheet)sheets.get_Item(1);
int recordCount = 2;//从第二行开始判断
//查找数据行数
while (true)
{
if (Method.IsNumber(worksheet.Cells[recordCount, 1].TEXT))
{
recordCount++;
}
else
{
break;
}
}
MergeWGWCell(ref worksheet, 2, recordCount, 2);
//MergePHCell(ref worksheet, 2, recordCount, 6);
workbook.SaveAs(file);
app.Quit();
}
/// <summary>
/// 格式化表格
/// </summary>
/// <param name="file"></param>
public static void FormateExcel(string file)
{
Application app = new Application();
app.DisplayAlerts = false;
app.Visible = false;
app.UserControl = true;
Workbooks workbooks = app.Workbooks;
_Workbook workbook = workbooks.Add(file);
Sheets sheets = workbook.Sheets;
for (int i = 1; i <= 1; i++) //读取2009-2017表格，首张为汇总表
{
Worksheet worksheet = (Worksheet)sheets.get_Item(i);
int recordCount = 2;//从第二行开始判断
//查找数据行数
while (true)
{
if (Method.IsNumber(worksheet.Cells[recordCount, 1].TEXT))
{
recordCount++;
}
else
{
break;
}
}
MergeCell(ref worksheet, 2, recordCount, 3);
MergeCell(ref worksheet, 2, recordCount, 7);
}
workbook.SaveAs(file);
app.Quit();
}
private async void CalculateRowSize()
{
int row_size = 0;
Application application = null;
Workbooks wbooks = null;
Workbook sourceWorkbook = null;
try
{
application = new Application();
wbooks = application.Workbooks;
listbox_excellPaths.Enabled = false;
foreach (ExcellFile item in excellFilePaths)
{
if (item.RowSize == 0)
{
WriteConsole($"{item.FileName} Boyutu Okunuyor...");
listbox_excellPaths.SelectedItem = item;
await Task.Run(() =>
{
sourceWorkbook = wbooks.Open(item.Path);
Worksheet wsheet = (Worksheet)sourceWorkbook.Worksheets.get_Item(1);
row_size += wsheet.UsedRange.Rows.Count;
item.RowSize = wsheet.UsedRange.Rows.Count;
sourceWorkbook?.Close();
Marshal.ReleaseComObject(sourceWorkbook);
});
int index = listbox_excellPaths.Items.IndexOf(item);
listbox_excellPaths.Items.Remove(item);
listbox_excellPaths.Items.Insert(index, item);
}
else
{
row_size += item.RowSize;
}
SetRowCount(row_size);
}
listbox_excellPaths.Enabled = true;
wbooks?.Close();
Marshal.ReleaseComObject(wbooks);
application?.Quit();
Marshal.ReleaseComObject(application);
WriteConsole($"Toplam Satır Boyutu {row_size} olarak okundu.");
MessageBox.Show($"{row_size}", "Toplam Satır Sayısı");
}
catch { }
finally
{
try
{
//sourceWorkbook?.Close();
//Marshal.ReleaseComObject(sourceWorkbook);
}
catch (Exception) { }
application = null;
}
}
/// <summary>
/// Excelリソース解放
/// </summary>
/// <param name="ReleaseMode">リリース対象Enum</param>
private void ReleaseExcelComObject(EnumReleaseMode ReleaseMode)
{
try
{
// xlSheet解放
if (xlSheet != null)
{
Marshal.ReleaseComObject(xlSheet);
xlSheet = null;
}
if (ReleaseMode == EnumReleaseMode.Sheet)
{
return;
}
// xlSheets解放
if (xlSheets != null)
{
Marshal.ReleaseComObject(xlSheets);
xlSheets = null;
}
if (ReleaseMode == EnumReleaseMode.Sheets)
{
return;
}
// xlBook解放
if (xlBook != null)
{
Marshal.ReleaseComObject(xlBook);
xlBook = null;
}
if (ReleaseMode == EnumReleaseMode.Book)
{
return;
}
// xlBooks解放
if (xlBooks != null)
{
Marshal.ReleaseComObject(xlBooks);
xlBooks = null;
}
if (ReleaseMode == EnumReleaseMode.Books)
{
return;
}
// xlApp解放
if (xlApp != null)
{
Marshal.ReleaseComObject(xlApp);
}
}
catch (Exception)
{
throw;
}
}
// Read the first worksheet from a template document and extract and store
// the page settings for later use
protected static void SetPageOptionsFromTemplate(Application app, Workbooks workbooks, Hashtable options, ref Hashtable templatePageSetup)
{
if (!HasTemplateOption(options))
{
return;
}
try
{
var template = workbooks.Open((string)options["template"]);
AddCOMDelay(options);
if (template != null)
{
// Run macros from template if the /excel_template_macros option is given
if ((bool)options["excel_template_macros"])
{
var eventsEnabled = app.EnableEvents;
app.EnableEvents = true;
template.RunAutoMacros(XlRunAutoMacro.xlAutoOpen);
app.EnableEvents = eventsEnabled;
}
var templateSheets = template.Worksheets;
if (templateSheets != null)
{
// Copy the page setup details from the first sheet or chart in the template
if (templateSheets.Count > 0)
{
PageSetup tps = null;
var firstItem = templateSheets[1];
if (firstItem is _Worksheet)
{
tps = ((_Worksheet)firstItem).PageSetup;
}
else if (firstItem is _Chart)
{
tps = ((_Chart)firstItem).PageSetup;
}
var tpsType = tps.GetType();
for (int i = 0; i < templateProperties.Length; i++)
{
var prop = tpsType.InvokeMember(templateProperties[i], System.Reflection.BindingFlags.GetProperty, null, tps, null);
if (prop != null)
{
templatePageSetup[templateProperties[i]] = prop;
}
}
Converter.ReleaseCOMObject(firstItem);
}
ReleaseCOMObject(templateSheets);
}
CloseExcelWorkbook(template);
}
ReleaseCOMObject(template);
}
finally
{
}
}
public Task Send(SysData.DataTable table, string excelTemplatePath, bool directlyPrint, int startRowIndex = 2)
{
if (table == null)
{
throw new ArgumentNullException("table");
}
if (string.IsNullOrEmpty(excelTemplatePath))
{
throw new ArgumentNullException("excelTemplatePath", "You must specify path to the excel workbook");
}
Task tsk = Task.Run(() =>
{
_Application excelApp = null;
Workbooks excelWorkbooks = null;
_Workbook excelWorkbook = null;
_Worksheet excelSheet = null;
string fileNamePath = excelTemplatePath;;
//Start Excel and create new workbook from the template
excelApp = StartExcel();
try
{
excelWorkbooks = excelApp.Workbooks;
excelWorkbook = excelWorkbooks.Open(fileNamePath);
excelSheet = excelWorkbook.Sheets[1];
//Insert the DataGridView into the excel spreadsheet
TableToExcelSheet(table, excelSheet, startRowIndex, 1);
//if visible , then exit so user can see it, otherwise save and exit
if (directlyPrint)
{
//bool okPrint = objExcel.Dialogs[XlBuiltInDialog.xlDialogPrint].Show();
// if (!okPrint)
//{
// objWorkbook.Close(false);
// return;
//}
excelSheet.PrintOut();
//excelWorkbook.Close(false);
excelApp.DisplayAlerts = false;
excelApp.Quit();
ReleaseResources(excelApp, excelWorkbooks, excelWorkbook, excelSheet);
return;
}
excelApp.Visible = true;
ReleaseResources(excelApp, excelWorkbooks, excelWorkbook, excelSheet);
}
catch (Exception)
{
ForceExcleToQuit(excelApp);
ReleaseResources(excelApp, excelWorkbooks, excelWorkbook, excelSheet);
throw;
}
}
);
return(tsk);
}
private static void CreateExcelComponents(out Application app, out Workbooks workBooks, out Workbook workbook, out Sheets worksheets, out Worksheet worksheet)
{
app = new Application();
workBooks = app.Workbooks;
workbook = workBooks.Add(Type.Missing);
worksheets = workbook.Sheets;
worksheet = (Worksheet)worksheets[1];
}
public void Save()
{
excelapp.DisplayAlerts = false;
excelappworkbooks = excelapp.Workbooks;
excelappworkbook = excelappworkbooks[1];
//excelappworkbook.Saved = true;
excelappworkbook.Save();
}
private void ShowStartDocument()
{
_app.Visible = true;
_wbs = _app.Workbooks;
_wb = _wbs.Open(Path.GetFullPath(PathResolver.GenerateExcelPracticePath(DocumentPath)));
_wb.Activate();
_wb.BeforeClose += OnBeforeCloseExcel;
}
/// <summary>
/// Create Excel application parameters instances
/// </summary>
private void CreateExcelRef()
{
_ExcelApp = new Excel.Application();
_books = (Workbooks)_ExcelApp.Workbooks;
_book = (_Workbook)(_books.Add(_optionalValue));
_sheets = (Sheets)_book.Worksheets;
_sheet = (_Worksheet)(_sheets.get_Item(1));
}
/// <summary>
/// Metodo que crea una aplicacion de Excel
/// </summary>
private void CrearExcel()
{
excelApp = new Application();
libros = excelApp.Workbooks;
libro = libros.Add(valorOpcional);
hojas = libro.Worksheets;
hoja = (Worksheet)(hojas.Item[1]);
}
/// <summary>
/// This constructor determines the exel file being worked on and also prints the headers
/// for the report.
/// </summary>
/// <param name="excel"> The current instance of excel. </param>
/// <param name="wbs"> The workbooks instance. </param>
/// <param name="wb"> The current workbook being used. </param>
public Printer(_Application excel, Workbooks wbs, _Workbook wb)
{
this.excel = excel;
this.wbs = wbs;
this.wb = wb;
this.ws = wb.Worksheets[sheetNumber];
printHeaders();
}
/// Create Excel application parameters instances
private void CreateExcelRef()
{
excelApp = new Microsoft.Office.Interop.Excel.Application();
books = (Workbooks)excelApp.Workbooks;
book = (Workbook)(books.Add(optionalValue));
sheets = (Sheets)book.Worksheets;
sheet = (Worksheet)(sheets.get_Item(1));
}
public DataGridExcelTools()
{
CursorPosition = 1;
excel = Microsoft.VisualBasic.Interaction.CreateObject("Excel.Application", string.Empty);
//excel.ScreenUpdating = false;
workbook = excel.workbooks;
workbook.Add();
worksheet = excel.ActiveSheet;
}
public XlsReportGenerator()
{
this.app = new Application { Visible = false };
this.workbooks = this.app.Workbooks;
this.workbook = this.workbooks.Add(Missing.Value);
this.worksheet = this.workbook.ActiveSheet;
this.worksheet.Name = "Sheet1";
this.rnd = new Random();
}
public ExcelHelper(string workbookPath)
{
//Get reference to Excel.Application from the ROT.
_xlApp = new Application();
_xlWorkBooks = _xlApp.Workbooks;
//Display the name of the object.
_xlWorkBook = _xlApp.Workbooks.Open(
workbookPath, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0
);
}
/// <summary>
/// 指定されたパラメタを元にインスタンスを生成します。
/// </summary>
/// <param name="quitFlg">終了フラグ</param>
public ExcelApplication(bool quitFlg = true)
{
// リソース解放フラグ設定
this.disposed = false;
// Excelアプリケーションインスタンス生成
Type excelAppType = Type.GetTypeFromProgID(ExcelApplication.ExcelApplicationProgramId, true);
interopExcelApplication = Activator.CreateInstance(excelAppType);
// ワークブックコレクション生成
this.workbooks = new Workbooks(this.interopExcelApplication.Workbooks);
// 終了フラグ設定
this.quitFlg = quitFlg;
}
public void DisposeExcelObject()
{
try
{
if (workbook != null)
workbook.Close(true);
if (application != null)
{
if (application.Workbooks != null)
application.Workbooks.Close();
application.Quit();
}
if (range != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
if (workbook != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
if (workbooks != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
if (sheets != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
if (sheets != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
if (application != null)
System.Runtime.InteropServices.Marshal.ReleaseComObject(application);
range = null;
workbook = null;
workbooks = null;
sheets = null;
worksheet = null;
application = null;
Process[] pProcess;
pProcess = Process.GetProcessesByName("Excel");
foreach (var process in pProcess)
{
process.Kill();
}
}
catch (Exception ex)
{
}
}
public void Open(string filePath)
{
excel = new Application {DisplayAlerts = false};
workbooks = excel.Workbooks;
if (File.Exists(filePath))
{
workbook = workbooks.Open(filePath);
}
else
{
workbook = workbooks.Add();
workbook.SaveAs(filePath);
}
worksheet = workbook.Worksheets[1] as Worksheet;
if (worksheet == null) throw new NullReferenceException("worksheet");
}
public void Close()
{
if (worksheet != null)
{
Marshal.FinalReleaseComObject(worksheet);
worksheet = null;
}
if (workbook != null)
{
workbook.Close();
Marshal.FinalReleaseComObject(workbook);
workbook = null;
}
if (workbooks != null)
{
Marshal.FinalReleaseComObject(workbooks);
workbooks = null;
}
if (excel != null)
{
excel.Quit();
Marshal.FinalReleaseComObject(excel);
excel = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
public void GenerateExcel(DataSet receiveDS, string filePath, int rowcout)
{
ds = receiveDS;
strFileName = filePath;
try
{
application = new Application();
application.Visible = false;//true;
application.DisplayAlerts = false;
workbooks = application.Workbooks;
workbook = (Workbook)application.Workbooks.Add(1);
workbook.SaveAs(strFileName, XlFileFormat.xlWorkbookNormal, null, null, false, false,
XlSaveAsAccessMode.xlExclusive, false, false, null, null, false);
range = ((Worksheet)workbook.ActiveSheet).get_Range("A1", "AZ1");
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//range = worksheet.Cells;
int excelSheetRow = this.WriteDataToTheSpecifiedRange(ds.Tables[0], range, rowcout);
range = ((Worksheet)workbook.ActiveSheet).get_Range("A1", "AZ1");
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//range = worksheet.Cells;
//this.OverWriteDataToTheSpecifiedRange(ds.Tables[skuDataTableIndex], range);
//worksheet.get_Range("A1", "AZ1").EntireColumn.AutoFit();
//worksheet.SaveAs(strFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Worksheet XLsheet = ((Worksheet)workbook.ActiveSheet);
XLsheet.Columns.AutoFit();
XLsheet.Name = ds.Tables[0].TableName;
workbook.Save();
}
catch (Exception exp)
{
//string path = System.Web.HttpContext.Current.Server.MapPath(@"");
//path = path.Substring(0, path.Length - 3);
//System.IO.File.AppendAllText("LeaveYearTemp" + "/errorLog.txt", exp.Message);
throw exp;
}
finally
{
DisposeExcelObject();
}
}
public void GenerateExcelWithHeaderForPdf(DataSet receiveDS, string filePath, int rowcout, string companyName, string companyAddress, int columnScape, bool istotal, int itemPerPage)
{
ds = receiveDS;
strFileName = filePath;
try
{
application = new Application();
application.Visible = false;//true;
application.DisplayAlerts = false;
workbooks = application.Workbooks;
workbook = (Workbook)application.Workbooks.Add(1);
workbook.SaveAs(strFileName, XlFileFormat.xlWorkbookNormal, null, null, false, false,
XlSaveAsAccessMode.xlExclusive, false, false, null, null, false);
range = ((Worksheet)workbook.ActiveSheet).get_Range("A1", "AZ1");
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
Worksheet XLsheet = ((Worksheet)workbook.ActiveSheet);
int excelSheetRow = this.WriteDataToTheSpecifiedRangeForPdf(ds.Tables[0], range, rowcout, itemPerPage, XLsheet, rowcout, companyName, columnScape);
//if (istotal)
//{
// var colCount = ds.Tables[0].Columns.Count;
// var dataRowCount = ds.Tables[0].Rows.Count;
// var headerRangeTotal = XLsheet.Range[XLsheet.Cells[excelSheetRow + 1, 1], XLsheet.Cells[excelSheetRow + 1, columnScape]];
// headerRangeTotal[1, 1] = "Total";
// headerRangeTotal.Font.Bold = true;
// headerRangeTotal.Font.Size = 12;
// headerRangeTotal.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#CC99FF"));
// headerRangeTotal.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
// headerRangeTotal.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
// headerRangeTotal.Merge(Type.Missing);
// headerRangeTotal.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// //int i = columnScape - 1;
// for (int i = 0; i < colCount - columnScape; i++)
// {
// //var rangeTotal = XLsheet.Range[XLsheet.Cells[rowcout + dataRowCount + 1, i + columnScape + 1], XLsheet.Cells[rowcout + dataRowCount + 1, i + columnScape + 1]];
// var rangeTotal = XLsheet.Range[XLsheet.Cells[excelSheetRow + 1, i + columnScape + 1], XLsheet.Cells[excelSheetRow + 1, i + columnScape + 1]];
// string startColumnLetter = ExcelColumnLetter(i + columnScape);
// string endColumnLetter = ExcelColumnLetter(i + columnScape);
// rangeTotal[1, 1] = "=SUM(" + startColumnLetter + "" + (rowcout + 1) + ":" + endColumnLetter + "" + (rowcout + dataRowCount) + ")";
// rangeTotal[1, 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#CC99FF"));
// rangeTotal[1, 1].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
// rangeTotal[1, 1].Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
// }
//}
range = ((Worksheet)workbook.ActiveSheet).get_Range("A1", "AZ1");
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
XLsheet.PageSetup.PrintTitleRows = "$1:$6";
XLsheet.Columns.AutoFit();
XLsheet.Name = ds.Tables[0].TableName;
workbook.Save();
}
catch (Exception exp)
{
throw exp;
}
finally
{
DisposeExcelObject();
}
}
public void GenerateExcelWithHeader(DataSet receiveDS, string filePath, int rowcout, string companyName, string companyAddress, int columnScape, bool istotal, DateTime salaryMonth)
{
ds = receiveDS;
strFileName = filePath;
try
{
application = new Application();
application.Visible = false;//true;
application.DisplayAlerts = false;
workbooks = application.Workbooks;
workbook = (Workbook)application.Workbooks.Add(1);
workbook.SaveAs(strFileName, XlFileFormat.xlWorkbookNormal, null, null, false, false,
XlSaveAsAccessMode.xlExclusive, false, false, null, null, false);
range = ((Worksheet)workbook.ActiveSheet).get_Range("A1", "AZ1");
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//range = worksheet.Cells;
Worksheet XLsheet = ((Worksheet)workbook.ActiveSheet);
#region Main Header
// if (companyName != "")
//{
var headerRange1 = XLsheet.Range[XLsheet.Cells[1, 1], XLsheet.Cells[rowcout - 2, 6]];
//headerRange1[1, 1] = companyName;
headerRange1.Font.Bold = true;
headerRange1.Font.Size = 16;
//headerRange1.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#CC99FF"));
//headerRange1.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//headerRange1.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
headerRange1.Merge(Type.Missing);
headerRange1.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//var picPath = "G:\\Project\\Asp.Net\\Azolution\\Empress_4_0_0\\Empress_3_0_0\\Images\\" + "report-logo.png";
string picPath = System.Web.HttpContext.Current.Server.MapPath("../Images/report-logo.png");
object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Range picPosition = headerRange1;//GetPicturePosition(); // retrieve the range for picture insert
Microsoft.Office.Interop.Excel.Pictures p = XLsheet.Pictures(missing) as Microsoft.Office.Interop.Excel.Pictures;
Microsoft.Office.Interop.Excel.Picture pic = null;
pic = p.Insert(picPath, missing);
pic.Left = Convert.ToDouble(picPosition.Left);
pic.Top = picPosition.Top;
// }
if (companyName != "")
{
var headerRange2 = XLsheet.Range[XLsheet.Cells[rowcout - 3, 7], XLsheet.Cells[rowcout - 3, 14]];
headerRange2[1, 1] = companyName;//companyAddress;
headerRange2.Font.Bold = true;
headerRange2.Font.Size = 12;
//headerRange2.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#CC99FF"));
//headerRange2.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
//headerRange2.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
headerRange2.Merge(Type.Missing);
headerRange2.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
// if (companyName != "")
// {
var headerRange3 = XLsheet.Range[XLsheet.Cells[rowcout - 1, 1], XLsheet.Cells[rowcout - 1, 6]];
headerRange3[1, 1] = "Salary Control Register for the Month of " + salaryMonth.ToString("MMMM-yyyy");//companyAddress;
headerRange3.Font.Bold = true;
headerRange3.Font.Size = 12;
headerRange3.Merge(Type.Missing);
headerRange3.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
//}
//var headerRang = XLsheet.get_Range(XLsheet.Cells[1, 1], XLsheet.Cells[1, 43]);
#endregion
#region Print DateTime
//var headerRangeSystem = XLsheet.Range[XLsheet.Cells[rowcout - 1, ds.Tables[0].Columns.Count - 4], XLsheet.Cells[rowcout - 1, ds.Tables[0].Columns.Count]];
//headerRangeSystem[1, 1] = "Print From Rapid 2.0";
//headerRangeSystem.Font.Bold = true;
//headerRangeSystem.Font.Size = 10;
//headerRangeSystem.Merge(Type.Missing);
//headerRangeSystem.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//var headerRangeDateTimeVal = XLsheet.Range[XLsheet.Cells[rowcout - 2, ds.Tables[0].Columns.Count - 4], XLsheet.Cells[rowcout - 2, ds.Tables[0].Columns.Count]];
//headerRangeDateTimeVal[1, 1] = "Print Date & Time : " + DateTime.Now.ToString("MMMM dd, yyyy") + DateTime.Now.ToString("h:mm tt");//+ DateTime.Now.ToString("d-MMM-yyyy HH.mm.ss tt", CultureInfo.InvariantCulture);//DateTime.Now.ToString("dd MMM yyyy hh:mm:ss");//companyAddress;
//headerRangeDateTimeVal.Font.Bold = true;
//headerRangeDateTimeVal.Font.Size = 10;
//headerRangeDateTimeVal.Merge(Type.Missing);
//headerRangeDateTimeVal.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
#endregion
int excelSheetRow = this.WriteDataToTheSpecifiedRange(ds.Tables[0], range, rowcout);
if (istotal)
{
var colCount = ds.Tables[0].Columns.Count;
var dataRowCount = ds.Tables[0].Rows.Count;
var headerRangeTotal = XLsheet.Range[XLsheet.Cells[rowcout + dataRowCount + 1, 1], XLsheet.Cells[rowcout + dataRowCount + 1, columnScape]];
headerRangeTotal[1, 1] = "Total";
headerRangeTotal.Font.Bold = true;
headerRangeTotal.Font.Size = 12;
headerRangeTotal.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#CC99FF"));
headerRangeTotal.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
headerRangeTotal.Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
headerRangeTotal.Merge(Type.Missing);
headerRangeTotal.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//int i = columnScape - 1;
for (int i = 0; i < (colCount - columnScape)+1; i++)
{
var rangeTotal = XLsheet.Range[XLsheet.Cells[rowcout + dataRowCount + 1, i + columnScape + 1], XLsheet.Cells[rowcout + dataRowCount + 1, i + columnScape + 1]];
string startColumnLetter = ExcelColumnLetter(i + columnScape);
string endColumnLetter = ExcelColumnLetter(i + columnScape);
rangeTotal[1, 1] = "=SUM(" + startColumnLetter + "" + (rowcout + 1) + ":" + endColumnLetter + "" + (rowcout + dataRowCount) + ")";
rangeTotal[1, 1].Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#CC99FF"));
rangeTotal[1, 1].Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
rangeTotal[1, 1].Borders.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
}
}
range = ((Worksheet)workbook.ActiveSheet).get_Range("A1", "AZ1");
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//range = worksheet.Cells;
//this.OverWriteDataToTheSpecifiedRange(ds.Tables[skuDataTableIndex], range);
//worksheet.get_Range("A1", "AZ1").EntireColumn.AutoFit();
//worksheet.SaveAs(strFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing);
XLsheet.PageSetup.PrintTitleRows = "$1:$5";
// XLsheet.PageSetup.RightHeader = "Page &P of &N";
XLsheet.PageSetup.LeftFooter = "-------------------------------------\n &BPrepared By&B \n " + "Rapid 2.0| Payroll | Print Date, Time && Page No : " + DateTime.Now.ToString("MMMM dd, yyyy") + " | " + DateTime.Now.ToString("h:mm tt") + " | " + "Page &P of &N";
XLsheet.PageSetup.BottomMargin = application.InchesToPoints(1.5);
XLsheet.PageSetup.CenterFooter = "-------------------------------------\n &BChecked By&B \n";
XLsheet.PageSetup.RightFooter = "-------------------------\n &BApproved By&B \n";
XLsheet.Columns.AutoFit();
XLsheet.Name = ds.Tables[0].TableName;
workbook.Save();
}
catch (Exception exp)
{
//string path = System.Web.HttpContext.Current.Server.MapPath(@"");
//path = path.Substring(0, path.Length - 3);
//System.IO.File.AppendAllText("LeaveYearTemp" + "/errorLog.txt", exp.Message);
throw exp;
}
finally
{
DisposeExcelObject();
}
}
public void Clean()
{
// Show excel app
excel.ScreenUpdating = true;
excel.Visible = true;
//Collect
try
{
Marshal.ReleaseComObject(rg);
Marshal.ReleaseComObject(rgHeader);
Marshal.ReleaseComObject(worksheet);
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(excel);
}
catch (Exception e)
{
}
rg = null;
rgHeader = null;
worksheet = null;
workbook = null;
excel = null;
GC.Collect();
}
private void SaveDirectory(Workbooks workbooks, DirectoryInfo currentInputDirectory, DirectoryInfo currentOutputDirectory)
{
foreach(var subDir in currentInputDirectory.GetDirectories())
{
SaveDirectory(workbooks, subDir, currentOutputDirectory.CreateSubdirectory(subDir.Name));
}
foreach(var file in currentInputDirectory.GetFiles( "*.csv", SearchOption.TopDirectoryOnly))
{
if(!currentOutputDirectory.Exists)
{
currentOutputDirectory.Create();
}
SaveWorkbook(workbooks, file.FullName, Path.Combine(currentOutputDirectory.FullName, Path.GetFileNameWithoutExtension(file.Name) + ".xlsx"));
}
}
//创建一个Excel对象
public void Create()
{
app = new Application();
wbs = app.Workbooks;
wb = wbs.Add(true);
}
//打开一个Excel文件
public void Open(string FileName)
{
app = new Application();
wbs = app.Workbooks;
wb = wbs.Add(FileName);
//wb = wbs.Open(FileName, 0, true, 5,"", "", true, XlPlatform.xlWindows, "t", false, false, 0, true,Type.Missing,Type.Missing);
//wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
mFilename = FileName;
}
public void Dispose()
{
#if DEBUG
Console.WriteLine("Cleaning up");
#endif
_isDisposed = true;
if (_worksheet != null) {
for (int i = 0; i < _worksheet.Length; i++) {
if (_worksheet[i] != null) {
Marshal.ReleaseComObject(_worksheet[i]);
}
_worksheet[i] = null;
}
}
_worksheet = null;
if (_wTemplate != null)
{
_wTemplate.Close(false, Missing.Value, Missing.Value);
Marshal.ReleaseComObject(_wTemplate);
}
_wTemplate = null;
if (_workbooks != null)
{
_workbooks.Close();
Marshal.ReleaseComObject(_workbooks);
}
_workbooks = null;
if (_excel != null)
{
_excel.Quit();
Marshal.ReleaseComObject(_excel);
}
_excel = null;
}
public void OpenTemplate()
{
#if DEBUG
Console.WriteLine("Opening up excel template file");
#endif
if (_excel == null || _isDisposed)
throw new ObjectDisposedException("excel", "Either the excel app has been closed, or it was never opened");
if (!File.Exists(_loadPathTemplate))
throw new FileNotFoundException(string.Format("Unable to find template file at path: {0}", _loadPathTemplate));
_workbooks = _excel.Workbooks;
_wTemplate = _workbooks.Open(_loadPathTemplate, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
_worksheet = new Worksheet[] { _wTemplate.Worksheets[1], _wTemplate.Worksheets[2], _wTemplate.Worksheets[3] };
}
private static void SaveWorkbook(Workbooks workbooks, string inPath, string outPath)
{
Workbook workbook = null;
try
{
workbook = workbooks.Open(inPath);
workbook.SaveAs(outPath, XlFileFormat.xlWorkbookDefault);
}
finally
{
if(workbook != null)
{
workbook.Close();
Marshal.FinalReleaseComObject(workbook);
}
}
}
public ExcelCmd()
{
ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelWorkbooks = ExcelApp.Workbooks;
}
protected override bool OpenInternal(string fileName, bool write)
{
if (_obj != null) return false;
_app = new Application();
_app.Visible = false;
_app.DisplayAlerts = false;
_app.ScreenUpdating = false;
_workbooks = _app.Workbooks;
if (!_write && File.Exists(fileName))
{
_obj = _workbooks.Open(fileName);
}
else
{
_obj = _workbooks.Add();
}
_worksheets = _obj.Worksheets;
return true;
}
//关闭一个Excel对象，销毁对象
public void Close()
{
//wb.Save();
wb.Close(Type.Missing, Type.Missing, Type.Missing);
wbs.Close();
app.Quit();
wb = null;
wbs = null;
app = null;
GC.Collect();
}
private void openExcel(String filename)
{
app.Visible = false;
this.wbks = app.Workbooks;
r_wbk= wbks.Add(filename);
}
private void Form1_Load(object sender, EventArgs e)
{
周次数.Text = GetWeekOfYear().ToString();
excelApplication = null;//Excel Application Object
excelWorkBooks = null;//Workbooks
excelWorkBook = null;//Excel Workbook Object
excelWorkSheet = null;//Excel Worksheet Object
ActiveSheetIndex = 1;
System.Data.DataTable dt = new System.Data.DataTable();
dt.TableName = "table1";
dt.AcceptChanges();
}
private void initExcel()
{
try {
excelApp = new ApplicationClass();
workbooks = excelApp.Workbooks;
workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
worksheet =(Worksheet) workbook.Worksheets[1];
worksheet.Cells[1, 1] = "No";
worksheet.Cells[1, 2] = "Picture";
worksheet.Cells[1, 3] = "Time";
worksheet.Cells[1, 4] = "CorrectRate";
worksheet.Cells[1, 5] = "MaxGrasp";
worksheet.Cells[1, 6] = "MaxSpeed";
worksheet.Cells[1, 7] = "DetailData";
}
catch(Exception exp){
throw(exp);
}
}
internal WorkbookList(Workbooks workbooks)
{
this.workbooks = workbooks;
}
