public static List <List <string> > Read(string fileName, int columnCount)
{
List <List <string> > textList = new List <List <string> >();
excelApp = new ApplicationClass();
Workbooks myWorkBooks = excelApp.Workbooks;
myWorkBooks.Open(fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
Sheets sheets = excelApp.Sheets;
mySheet1 = (Worksheet)sheets[1];
int rowCount = mySheet1.UsedRange.Rows.Count;
if (rowCount != 0)
{
for (int i = 3; i <= rowCount; i++)
{
string name = ((Range)mySheet1.Cells[i, 2]).Text.ToString();
if (name != "")
{
List <string> list = new List <string>();
for (int j = 0; j < columnCount; j++)
{
list.Add(((Range)mySheet1.Cells[i, j + 1]).Text.ToString());
}
textList.Add(list);
}
}
}
myWorkBooks.Close();
excelApp.Quit();
excelApp = null;
return(textList);
}
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 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];
}
}
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;
}
/// <summary>
/// Open an existing Excel workbook.
/// </summary>
/// <param name="filename">path of Excel file to open</param>
/// <param name="worksheet">optional worksheet to set as the active sheet</param>
public void Open(string filename, string worksheet = "")
{
// Open the specified workbook.
_workbooks = _excelApp.Workbooks;
_workbook = _workbooks.Open(filename);
// If the object was not created...
if (_workbook == null)
{
// The file was not able to be opened.
throw new Exception("Unable to open Excel workbook.");
}
// If a worksheet was not specified...
if (string.IsNullOrEmpty(worksheet))
{
// Act upon the active worksheet.
_worksheet = _workbook.ActiveSheet;
}
// If a worksheet was specified...
else
{
// Select an existing worksheet.
_worksheet = _workbook.Worksheets[worksheet];
}
}
private static HttpGetHelper httpGetHelper; //定义HttpGetHelper获取经纬度对象
public static void Process(string originalFileName, int originalColumn, int targetColumn, int rows)
{
try
{
string workTmp = originalFileName;
objExcelApp = new Application();
objExcelWorkBooks = objExcelApp.Workbooks;
objExcelWorkbook = objExcelWorkBooks.Open(workTmp, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
objExcelWorkSheet = (Worksheet)objExcelWorkbook.Worksheets[1];
httpGetHelper = new HttpGetHelper();
for (int i = 2; /*objExcelWorkSheet.Cells[i, originalColumn].Text.ToString() != ""*/ i < rows + 2; i++)
{
string address = objExcelWorkSheet.Cells[i, originalColumn].Text.ToString();
string str = httpGetHelper.GaoDeAnalysis("key=3e0ded4b2852e194c63565d151c2e606&address=" + address);
objExcelWorkSheet.Cells[i, targetColumn] = str;
}
string targetFileName = originalFileName.Insert(originalFileName.LastIndexOf('.'), "new");
objExcelWorkbook.SaveAs(targetFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
finally
{
objExcelApp.Quit();
}
}
/// <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);
}
}
public void Init()
{
_excelApp = new Excel.Application();
_excelApp.DisplayAlerts = false;
_excelApp.Visible = false;
_workbooks = _excelApp.Workbooks;
_wb = _workbooks.Open($@"{AppDomain.CurrentDomain.BaseDirectory}\blank2.xls");
SaveFileDialog dialog = new SaveFileDialog();
dialog.FileName = $"Акт списания {Extensions.RemoveIllegalCharsFromFilename(ActNumber)}. Сформировано {DateTime.Now.ToShortDateString()}.xls";
dialog.Filter = "Файлы Excel(*.xls; *.xlsx) | *.xls; *.xlsx";
dialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);//@"e:\";//
if (dialog.ShowDialog() != DialogResult.OK)
{
throw new Exception("Файл не сохранён!");
}
_wb.SaveAs(dialog.FileName);
this._fileName = dialog.FileName;
_wb.Close();
_wb = _excelApp.Workbooks.Open($@"{dialog.FileName}");
_excelApp.Visible = true;
_ws1 = _wb.Worksheets["стр.1"];
_ws2 = _wb.Worksheets["стр.2"];
_ws3 = _wb.Worksheets["стр.3"];
_ws2.Select();
_currentWS = _ws2;
}
private void ImportButton_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Title = "Выберите файл Excel";
openFileDialog.Filter = "Файлы Excel(*.xls;*.xlsx)|*.xls;*.xlsx";
openFileDialog.Multiselect = false;
if (!(openFileDialog.ShowDialog() == DialogResult.Cancel))
{
string fileExcel = openFileDialog.FileName;
Excel.Application excel = new Excel.Application();
Workbooks workbooks = excel.Workbooks;
Workbook workbook = workbooks.Open(fileExcel);
Worksheet worksheet = workbook.Worksheets.get_Item(1);
Range range = worksheet.UsedRange;
for (int i = 2; i <= range.Rows.Count; i++)
{
if (range.Cells[i, 1].Value != null && range.Cells[i, 2].Value != null && range.Cells[i, 3].Value != null)
{
CardsDataGridView.Rows.Add(range.Cells[i, 1].Value.ToString(), range.Cells[i, 2].Value.ToString(), range.Cells[i, 3].Value.ToString(), 4, LocationComboBox.SelectedValue);
}
}
workbooks.Close();
excel.Quit();
CardsPanel.Visible = true;
}
}
public Workbook OpenExcelByMSExcel(string filePath)
{
workbook = _objWorkbooks.Open(filePath, Type.Missing, false, true, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
return(workbook);
}
private static Dictionary <string, int> SelectData(string filename)
{
Application excelApplication = new Application();
Workbooks workbooks = excelApplication.Workbooks;
Workbook workbook = workbooks.Open(filename);
Sheets sheets = workbook.Worksheets;
Worksheet worksheet = (Worksheet)sheets.Item[1];
Range firstColumn = worksheet.UsedRange.Columns[1];
Range secondColumn = worksheet.UsedRange.Columns[2];
var keys = (Array)firstColumn.Cells.Value;
var values = (Array)secondColumn.Cells.Value;
var keysArray = keys.OfType <object>().Select(o => o.ToString()).ToArray();
var valuesArray = values.OfType <object>().Select(o => int.Parse(o.ToString())).ToArray();
var result = new Dictionary <string, int>();
for (int i = 0; i < keysArray.Count(); i++)
{
result.Add(keysArray[i], valuesArray[i]);
}
workbook.Close(filename);
excelApplication.Quit();
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(excelApplication);
return(result);
}
public void readExcelFile(int totalRows, int totalColumns)
{
try
{
string excelTemplate = System.AppDomain.CurrentDomain.BaseDirectory + "template.xlsx";
if (!File.Exists(excelTemplate))
{
throw new Exception("Excel template not found in " + excelTemplate);
}
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBooks = xlApp.Workbooks;
try
{
xlWorkBook = xlWorkBooks.Open(excelTemplate, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false); //open the template file!
}
catch (Exception e)
{
MessageBox.Show("template file is not spelled correctly or is not in the same directory as EBOMCreationTool.exe");
mainframe.end = true;
return;
}
xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(1); //worksheet to write data to
getCells(totalRows, totalColumns);
}
finally
{
mainframe.WriteToConsole("Finished reading Excel File");
}
}
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);
}
private void btnImport_Click(object sender, EventArgs e)
{
Workbooks workbooks = xlsApp.Workbooks;
workbook = workbooks.Open("D:\\temp\\user.xls");//索引从1开始
Worksheet sheet = workbook.Worksheets.Item[1];
textBox1.Text += sheet.Name + "\r\n";
textBox1.Text += sheet.Range["A1"].Value2 + "\r\n";
Range rows = sheet.Rows;
textBox1.Text += "一共 " + rows.Count + "行\r\n";
Range cells = sheet.Cells;
textBox1.Text += "一共 " + cells.Count + "个单元格\r\n";
//读取任意一个单元格
string val = ((Range)sheet.Cells[1, 1]).Text.ToString();
textBox1.Text += val + "\r\n";
for (int i = 0; i < 3; i++)
{
string val1 = ((Range)sheet.Cells[2, i + 1]).Text.ToString();
textBox1.Text += val1 + "\r\n";
}
}
public ActionResult ChildExcelOpen(string File)
{
try
{
string path = Server.MapPath("~/Content/UploadedFolder/File");
string openPath = "";
string[] filePaths = Directory.GetFiles(path, "*.xls");
foreach (var item in filePaths)
{
if (File == Path.GetFileName(item))
{
openPath = Path.Combine(Server.MapPath("~/Content/UploadedFolder/File"), Path.GetFileName(item));
}
}
//string patha = Server.MapPath("~/Content/Loan Calculation.xlsx");
var excelApp = new Application();
excelApp.Visible = true;
Workbooks books = excelApp.Workbooks;
Workbook sheet = books.Open(openPath);
// System.IO.File.Open(childpath, FileMode.Open);
//Application excel = new Application();
//var lstEmp = scheModel.tblExcelMasters.Where(x => x.MasterExcel_Id == id).FirstOrDefault();
return(Json("Success", JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
throw;
}
}
static public int prtPageSize = 10;//19
public Workbook OpenExcel(string fileName)
{
Application app = new Application();
Workbooks wbks = app.Workbooks;
return(wbks.Open(fileName));
}
public void OpenReportTempalte()
{
if (m_oExcelApp != null)
{
CloseReportTemplate();
}
// Create an instance of Microsoft Excel, make it visible,
// and open Book1.xls.
//m_oExcelApp = new Excel.ApplicationClass();
m_oExcelApp = GetExcelApplication();
m_oBooks = m_oExcelApp.Workbooks;
//m_oExcelApp.AskToUpdateLinks = false;
// IMPORTANT: IF YOU ARE USING EXCEL Version >= 10.0 Use function
// prototype in "EXCEL VERSION 10.0" section.
// For Excel Version 9.0 use default "EXCEL VERSION = 9.0".
// This application is not tested with versions lower than Excel 9.0
// Or greater than 10.0
// EXCEL VERSION 10.0
m_oBook = m_oBooks.Open(m_sReportTemplate, m_oMissing, m_oMissing,
m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing,
m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing);
// END EXCEL VERSION 10.0
// EXCEL VERSION 9.0
//m_oBook = m_oBooks.Open(m_sReportTemplate, m_oMissing, m_oMissing,
// m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing, m_oMissing,
// m_oMissing, m_oMissing, m_oMissing, m_oMissing,m_oMissing,m_oMissing);
// END EXCEL VERSION 9.0
m_oSheet = (_Worksheet)m_oBook.Worksheets[m_nReportIndex];
}
// 打开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 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 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;
}
}
// 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
{
}
}
private void ShowStartDocument()
{
_app.Visible = true;
_wbs = _app.Workbooks;
_wb = _wbs.Open(Path.GetFullPath(PathResolver.GenerateExcelPracticePath(DocumentPath)));
_wb.Activate();
_wb.BeforeClose += OnBeforeCloseExcel;
}
/// <summary>
/// 指定したファイルのエクセルを開く
/// </summary>
/// <param name="fileName"></param>
private void OpenExcelFile(string fileName)
{
if (xlBook == null)
{
this.xlBooks = xlApp.Workbooks;
this.xlBook = xlBooks.Open(fileName);
}
}
public ExcelFunction(string path, int Sheet)
{
excel.DisplayAlerts = false;
this.path = path;
wb = wbs.Open(this.path);
ws = (Worksheet)wb.Worksheets[Sheet];
range = ws.UsedRange;
}
/// <summary>
/// Opens the book.
/// </summary>
/// <param name="fileName">Name of the file.</param>
/// <param name="sheetNumber">The sheet number.</param>
public void OpenBook(string fileName, int sheetNumber)
{
using (new ExcelUILanguageHelper()) {
_application = new Application();
_books = _application.Workbooks;
_book = _books.Open(fileName, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue, _misValue);
_sheet = (Worksheet)_book.Worksheets.Item[sheetNumber];
}
}
public ExcelConfig()
{
app = new Application();
workbooks = app.Workbooks;
string projectPath = Directory.GetParent(Directory.GetCurrentDirectory()).Parent.FullName;
workbook = workbooks.Open(projectPath + "\\" + "ExcelMobileSite" + ".xlsx");
// workbook = workbooks.Open(@"F:\VS-SeleniumProjectDir\SeleniumTest1\SeleniumTest1\ExcelMobileSite.xlsx");
}
public static bool ConvertExcelToPdf(string inputFile, string outputfile)
{
string outputFileName = outputfile;
Microsoft.Office.Interop.Excel.Application excelApp =
new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = false;
Workbook workbook = null;
Workbooks workbooks = null;
try
{
//ExportAsFixedFormatXlFixedFormatType
workbooks = excelApp.Workbooks;
workbook = workbooks.Open(inputFile);
workbook.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, outputFileName,
XlFixedFormatQuality.xlQualityStandard, true, true, Type.Missing, Type.Missing, false, Type.Missing);
}
catch (Exception)
{
MessageBox.Show("Co loi xay ra");
return(false);
}
finally
{
if (workbook != null)
{
workbook.Close(XlSaveAction.xlDoNotSaveChanges);
while (Marshal.FinalReleaseComObject(workbook) != 0)
{
}
;
workbook = null;
}
if (workbooks != null)
{
workbooks.Close();
while (Marshal.FinalReleaseComObject(workbooks) != 0)
{
}
;
workbooks = null;
}
if (excelApp != null)
{
excelApp.Quit();
excelApp.Application.Quit();
while (Marshal.FinalReleaseComObject(excelApp) != 0)
{
}
;
excelApp = null;
}
}
return(true);
}
public async Task LoadWorkbookAsync(string filePath, bool isReadOnly = true)
{
await Task.Run(new System.Action(() =>
{
workbooks2 = xlApp.Workbooks;
xlWorkbook = workbooks2.Open(Filename: filePath, ReadOnly: isReadOnly);
}
));
}
public string ConvertExcelToPdf(string inputFile, string pdfPath)
{
Application excelApp = new Application();
excelApp.Visible = false;
Workbook workbook = null;
Workbooks workbooks = null;
try
{
workbooks = excelApp.Workbooks;
workbook = workbooks.Open(inputFile);
workbook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF,
pdfPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
}
finally
{
if (workbook != null)
{
workbook.Close(XlSaveAction.xlDoNotSaveChanges);
while (Marshal.FinalReleaseComObject(workbook) != 0)
{
}
;
workbook = null;
}
if (workbooks != null)
{
workbooks.Close();
while (Marshal.FinalReleaseComObject(workbooks) != 0)
{
}
;
workbooks = null;
}
if (excelApp != null)
{
excelApp.Quit();
excelApp.Application.Quit();
while (Marshal.FinalReleaseComObject(excelApp) != 0)
{
}
;
excelApp = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
return(pdfPath);
}
/// <summary>
/// Loads an embedded resource add-in.
/// </summary>
/// <param name="resourceName">Addin as 'embedded resource'</param>
/// <returns>File name of the temporary file that the resource
/// was written to.</returns>
internal string LoadAddinFromEmbeddedResource(string resourceName)
{
Stream resourceStream = typeof(Instance).Assembly
.GetManifestResourceStream(resourceName);
if (resourceStream == null)
{
Logger.Error("LoadAddinFromEmbeddedResource: Unable to read embedded resource '{0}'", resourceName);
throw new IOException("Unable to open resource stream " + resourceName);
}
string addinPath;
Workbook loadedAddin = FindWorkbook(resourceName);
if (loadedAddin == null)
{
string tempDir = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName());
Directory.CreateDirectory(tempDir);
addinPath = Path.Combine(tempDir, resourceName);
Stream tempStream = File.Create(addinPath);
resourceStream.CopyTo(tempStream);
tempStream.Close();
resourceStream.Close();
try
{
Logger.Info("LoadAddinFromEmbeddedResource: Loading...");
Workbooks.Open(addinPath);
}
catch (System.Runtime.InteropServices.COMException)
{
Logger.Warn("LoadAddinFromEmbeddedResource: COM exception caught, falling back to CorruptLoad");
DisableDisplayAlerts();
try
{
Workbooks.Open(addinPath, CorruptLoad: XlCorruptLoad.xlExtractData);
}
catch (System.Runtime.InteropServices.COMException e)
{
Logger.Fatal("LoadAddinFromEmbeddedResource: COM exception occurred after calling Workbooks.Open");
Logger.Fatal(e);
throw new XLToolbox.Excel.ExcelException("Excel failed to load the legacy Toolbox add-in", e);
}
finally
{
EnableDisplayAlerts();
}
}
Logger.Info("LoadAddinFromEmbeddedResource: Loaded {0}", addinPath);
}
else
{
addinPath = loadedAddin.FullName;
Logger.Info("LoadAddinFromEmbeddedResource: Already loaded, path is {0}", addinPath);
}
return(addinPath);
}
/// <summary>
/// Locates a workbook and returns it. If the workbook is loaded already,
/// it will not be re-opened. If the workbook cannot be located, this method
/// returns null.
/// </summary>
/// <param name="name">Name (either bare name or full name with path) of the
/// workbook being sought. If this is null or whitespace, the active workbook
/// is returned.</param>
/// <returns>Workbook or null</returns>
public Workbook LocateWorkbook(string name)
{
Predicate <Workbook> predicate;
if (Path.GetFileName(name) == name)
{
// Find workbook by name only
predicate = new Predicate <Workbook>(wb => wb.Name == name);
}
else
{
// Find workbook by full name (including path)
predicate = new Predicate <Workbook>(wb => wb.FullName == name);
}
Workbook foundWorkbook = null;
if (!String.IsNullOrWhiteSpace(name))
{
Logger.Info("LocateWorkbook: Locating {0}", name);
for (int i = 1; i <= Workbooks.Count; i++)
{
Workbook workbook = Workbooks[i];
if (predicate(workbook))
{
Logger.Info("LocateWorkbook: Workbook already loaded");
foundWorkbook = workbook;
break;
}
Bovender.ComHelpers.ReleaseComObject(workbook);
}
if (foundWorkbook == null)
{
Logger.Info("LocateWorkbook: Workbook not loaded, attempting to open it");
try
{
foundWorkbook = Workbooks.Open(name);
}
catch (Exception e)
{
Logger.Warn("LocateWorkbook: Failed to open workbook");
Logger.Warn(e);
}
}
}
else
{
Logger.Info("LocateWorkbook: Using active workbook");
foundWorkbook = Instance.Default.ActiveWorkbook;
}
if (foundWorkbook == null)
{
Logger.Warn("LocateWorkbook: Unable to locate workbook");
}
return(foundWorkbook);
}
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 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);
}
}
}
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;
}
