private void saveWorksheet(string iFilename, string iWorksheetName, Excel.XlFileFormat iFileFormat) { Excel.Worksheet aTempSheet = Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets.get_Item(iWorksheetName) as Excel.Worksheet; Excel.Workbook aTempWorkbook = Globals.ThisAddIn.Application.Workbooks.Add(Type.Missing) as Excel.Workbook; aTempSheet.Copy(Type.Missing, aTempWorkbook.Worksheets.get_Item(1)); aTempWorkbook.SaveAs(iFilename, iFileFormat, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); aTempWorkbook.Close(false, Type.Missing, Type.Missing); }
/// <summary> /// Return true if extension of saved files is the same /// </summary> /// <param name="oldExtensionInString"></param> /// <returns></returns> private bool IsCurrentExtensionCoincidence(string oldExtensionInString, Microsoft.Office.Interop.Excel.XlFileFormat extension) { if ((oldExtensionInString == ".xlsx" && extension == XlFileFormat.xlOpenXMLWorkbook) || (oldExtensionInString == ".xls" && extension == XlFileFormat.xlWorkbookNormal)) { return(true); } else { return(false); } }
public static string SaveAs(string filename, Excel.XlFileFormat xlType = Excel.XlFileFormat.xlOpenXMLWorkbook) { var path = string.Empty; Transaction(filename, (wkbk) => { wkbk.SaveAs(GetPathWithoutExtension(filename), (int)xlType); path = wkbk.FullName; }); return(path); }
public static bool createExcel(object[,] oDatas, Excel.XlFileFormat fileFormat, string sPath, out string sErrString) { bool flag = false; Excel.Application excel = new Excel.Application(); Excel.Workbook workbook = excel.Workbooks.Add(true); Excel.Worksheet worksheet = workbook.ActiveSheet as Excel.Worksheet; sErrString = ""; try { int rows = oDatas.GetLength(0); int clumns = oDatas.GetLength(1); Excel.Range CellStart = worksheet.Cells[1, 1]; Excel.Range CellEnd = worksheet.Cells[rows, clumns]; Excel.Range range = worksheet.Range[CellStart, CellEnd]; range.Value2 = oDatas; workbook.SaveAs(sPath, fileFormat, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); excel.DisplayAlerts = false; flag = true; } catch (Exception e) { sErrString = e.ToString(); } finally { //释放内存 if (workbook != null) { workbook.Close(Missing.Value, Missing.Value, Missing.Value); } if (excel.Workbooks != null) { excel.Workbooks.Close(); } if (excel != null) { excel.Quit(); } worksheet = null; workbook = null; excel = null; } return(flag); }
/// <summary> /// Convert existing Excel document to a specified extension and returns path to file /// </summary> public static string ConvertExcel(string FilePathRaw, string NewFileName, string DirectoryToSave, string NewFileExtension, Excel.XlFileFormat NewFileFormat) { //calculate file name with exxtension if (!NewFileName.EndsWith("." + NewFileExtension)) { NewFileName = NewFileName + "." + NewFileExtension; } //get absolute filePaths for both the new directory and the old file path string directoryToSave = String.IsNullOrEmpty(DirectoryToSave) ? Directory.GetCurrentDirectory() : Path.GetFullPath(DirectoryToSave); string xlsFilePath = Path.GetFullPath(FilePathRaw); //calculate new File Path string newFilePath = Path.Combine(directoryToSave, NewFileName); //perform conversion Excel.Application excelApp = new Excel.Application(); excelApp.Visible = false; excelApp.DisplayAlerts = false; //performing conversion operation try { Excel.Workbook eWorkbook = excelApp.Workbooks.Open(xlsFilePath, 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); eWorkbook.SaveAs(newFilePath, NewFileFormat, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); eWorkbook.Close(false, Type.Missing, Type.Missing); } catch { throw; } finally { //Close the App excelApp.Quit(); GC.Collect(); } return(newFilePath); }
//convert existing xls document to xlsx and returns path to file public static string ConvertExcel(string FilePathRaw, string NewFileName, string DirectoryToSave, string NewFileExtension, Excel.XlFileFormat NewFileFormat) { //calculate file name with exxtension if (!NewFileName.EndsWith("." + NewFileExtension)) { NewFileName = NewFileName + "." + NewFileExtension; } //get absolute filePaths for both the new directory and the old file path string directoryToSave = String.IsNullOrEmpty(DirectoryToSave)? Directory.GetCurrentDirectory():Path.GetFullPath(DirectoryToSave); string xlsFilePath = Path.GetFullPath(FilePathRaw); //calculate new File Path string newFilePath = Path.Combine(directoryToSave, NewFileName); //perform conversion Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application(); excelApp.Visible = false; excelApp.DisplayAlerts = false; Microsoft.Office.Interop.Excel.Workbook eWorkbook = excelApp.Workbooks.Open(xlsFilePath, 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); eWorkbook.SaveAs(newFilePath, NewFileFormat, //Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); eWorkbook.Close(false, Type.Missing, Type.Missing); excelApp.Quit(); GC.Collect(); /* Excel.Workbook openWorkBook = null; * * Excel.Application activeExcel = new Excel.Application(); * openWorkBook = activeExcel.Workbooks.Open(FilePathRaw); * openWorkBook.SaveAs(newFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8);*/ return(newFilePath); }
internal static ExcelFileFormat ToExcelFileFormat(this Xl.XlFileFormat xlFileFormat) { switch (xlFileFormat) { case Xl.XlFileFormat.xlCSV: return(ExcelFileFormat.CSV); case Xl.XlFileFormat.xlExcel8: return(ExcelFileFormat.XLS); case Xl.XlFileFormat.xlOpenXMLWorkbookMacroEnabled: return(ExcelFileFormat.XLSM); case Xl.XlFileFormat.xlOpenXMLWorkbook: return(ExcelFileFormat.XLSX); default: return(ExcelFileFormat.Other); } }
private void ToExcel(Excel.Workbook bookExcel, string path, Excel.XlFileFormat fileFormat) { object misValue = Missing.Value; try { bookExcel.SaveAs(path, fileFormat, misValue, misValue, false, false, Excel.XlSaveAsAccessMode.xlExclusive, Excel.XlSaveConflictResolution.xlLocalSessionChanges, misValue, misValue, misValue, misValue); } catch { string[] vs = path.Split('.'); path = string.Empty; for (int i = 0; i < vs.Length - 1; i++) { path += vs[i]; } path += " Mới ." + vs[vs.Length - 1]; bookExcel.SaveAs(path, fileFormat, misValue, misValue, false, false, Excel.XlSaveAsAccessMode.xlExclusive, Excel.XlSaveConflictResolution.xlLocalSessionChanges, misValue, misValue, misValue, misValue); } bookExcel.Close(true); }
public string Convert() { if (!new System.IO.FileInfo(_destinationFilePath).Exists) { Microsoft.Office.Interop.Excel.Application wordApp = new Microsoft.Office.Interop.Excel.Application(); wordApp.Visible = false; wordApp.DisplayAlerts = false; object wordTrue = (object)true; object wordFalse = (object)false; Microsoft.Office.Interop.Excel.XlFileFormat fltDocFormatTemp = Microsoft.Office.Interop.Excel.XlFileFormat.xlWebArchive; Microsoft.Office.Interop.Excel.XlSaveAsAccessMode fltXlSaveAsAccessMode = Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange; object fltDocFormat = fltDocFormatTemp;// 10; //For filtered HTML Output object missing = Type.Missing; Microsoft.Office.Interop.Excel.Workbook doc1 = wordApp.Workbooks.Open(_sourceFilePath, missing, wordFalse, missing, missing, missing, missing, missing, missing, wordFalse, wordFalse, wordFalse, missing, missing, missing); object fileName = _destinationFilePath; doc1.SaveAs(fileName, fltDocFormat, missing, missing, wordFalse, missing, fltXlSaveAsAccessMode, missing, missing, missing, missing, missing); try { doc1.Close(); } finally { } } return(_destinationFilePath); }
private void ToPDF(Excel.Workbook bookExcel, string path, Excel.XlFileFormat fileFormat) { bookExcel.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, path, Excel.XlFixedFormatQuality.xlQualityStandard); bookExcel.Saved = false; bookExcel.Close(); }
public void Save(string fileName, Excel.XlFileFormat format) { this._wk.SaveAs(fileName, FileFormat: format); }
private void Init() { InitializeComponent(); if (AppParams.ExcelVerion >= 12) { borderColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(178, 178, 178)); phimBgColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(242, 220, 219)); ctBgColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(146, 208, 80)); tietmucBgColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(217, 217, 217)); borderBgVTCV7 = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.FromArgb(22, 54, 92)); headerBgColorHTV =borderBgVTCV7; timeBgColorHTV = borderBgVTCV7; xlFormat = Excel.XlFileFormat.xlOpenXMLWorkbook; extension = ".xlsx"; } else { borderColor = System.Drawing.ColorTranslator.ToOle(Color.DarkGray); phimBgColor = System.Drawing.ColorTranslator.ToOle(Color.SandyBrown); ctBgColor = System.Drawing.ColorTranslator.ToOle(Color.Green); tietmucBgColor = System.Drawing.ColorTranslator.ToOle(Color.DarkGray); borderBgVTCV7 = System.Drawing.ColorTranslator.ToOle(Color.Blue); headerBgColorHTV = borderBgVTCV7; timeBgColorHTV = borderBgVTCV7; } selectDate = NgayPhatSong.Selection; startDate = NgayPhatSong.SelectionStart; endDate = NgayPhatSong.SelectionEnd; InitControls(); this.Load += new EventHandler(frmKhungLPSTuan_Load); this.FormClosing += new System.Windows.Forms.FormClosingEventHandler(frmInLPS_FormClosing); }
/// <summary> /// Resave in correct extension and return true if file was saved successfully /// </summary> public bool ResaveExcelFile(string folderPath, string filePath, string newFileName, byte extension, string oldName) { bool flag = false; currentTryCount = 0; FileWorker fileWorker = new FileWorker(); bool retry = true; byte maxTryCount = 20; while (retry && maxTryCount > currentTryCount) { try { xlApp = new Microsoft.Office.Interop.Excel.Application(); if (hWnd == 0) { try { hWnd = xlApp.Hwnd; } catch (Exception ex) { errorsMessages.Add(ex.Message); hWnd = 0; } } retry = false; currentExeption = null; } catch (Exception ex) { currentTryCount++; currentExeption = ex; errorsMessages.Add(ex.Message); if (hWnd == 0) { try { hWnd = xlApp.Hwnd; } catch (Exception ex1) { errorsMessages.Add(ex1.Message); hWnd = 0; } } lastException = ex; } } currentTryCount = 0; retry = true; if (currentExeption == null) { xlApp.Visible = false; xlApp.DisplayAlerts = false; xlApp.UserControl = false; while (retry && maxTryCount > currentTryCount) { try { xlWorkbook = xlApp.Workbooks.Open(filePath, 0, false, 5, "", "", true, XlPlatform.xlWindows, "", false, false, 0, false, 1, 0); retry = false; currentExeption = null; } catch (Exception ex) { errorsMessages.Add(ex.Message); currentTryCount++; currentExeption = ex; lastException = ex; } } currentTryCount = 0; retry = true; if (currentExeption == null) { newFileName = Helper.SplitExstansionByDotAndTakeName(newFileName); Microsoft.Office.Interop.Excel.XlFileFormat excelExstansion = PickExstansion(extension); string oldExtensionInString = Helper.GetExtension(filePath); string fullFilePathAndName = folderPath + @"\" + newFileName; while (retry && maxTryCount > currentTryCount) { try { xlWorkbook.SaveAs(fullFilePathAndName, excelExstansion, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); retry = false; currentExeption = null; } catch (Exception ex) { errorsMessages.Add(ex.Message); currentTryCount++; currentExeption = ex; lastException = ex; } } currentTryCount = 0; retry = true; if (currentExeption == null) { DisposeCOMObjects(); if (IsCurrentExtensionCoincidence(oldExtensionInString, excelExstansion) && Helper.IsStringContains(filePath, newFileName)) { flag = true; } } else { if (hWnd == 0) { try { hWnd = xlApp.Hwnd; } catch (Exception ex) { hWnd = 0; } } DisposeCOMObjects(); return(flag); } } else { if (hWnd == 0) { try { hWnd = xlApp.Hwnd; } catch (Exception ex) { errorsMessages.Add(ex.Message); hWnd = 0; } } DisposeCOMObjects(); return(flag); } } else { if (hWnd == 0) { try { hWnd = xlApp.Hwnd; } catch (Exception ex) { errorsMessages.Add(ex.Message); hWnd = 0; } } DisposeCOMObjects(); return(flag); } if (hWnd == 0) { try { hWnd = xlApp.Hwnd; } catch (Exception ex) { errorsMessages.Add(ex.Message); hWnd = 0; } } DisposeCOMObjects(); return(flag); }
// Returns true if the creation succeed, if not returns false public static bool CreateExcelFile(string name, string customUI = null) { // Validate overriding if (File.Exists(name)) { Console.Write("There is an excel file already created with this name and path. \nWould you like to override? (y/n) --> "); ConsoleKeyInfo key; int x = 0x0; int pos = Console.CursorLeft; bool canceled = true; while (x != 0x1B) { key = Console.ReadKey(); x = key.KeyChar; if (!(x == 0x59 || x == 0x79 || x == 0x4E || x == 0x6E)) { if (x != 0x1B) { if (x != 0x8) { Console.Write("\b \b"); } else { Console.Write(" "); } Console.CursorLeft = pos; } else { Console.Write("xn"); } } else { canceled = x == 0x4E || x == 0x6E; break; } } if (canceled) { Console.WriteLine("\nProccess Canceled"); return(false); } else { File.Delete(name); Console.WriteLine("\nOverriding file..."); } } // Gets the current office version Excel.Application xlApp; if (version == null) { xlApp = new Excel.Application(); version = xlApp.Version; xlApp.Quit(); while (Marshal.ReleaseComObject(xlApp) != 0) { } } DisableTrustCenterSecurity(); xlApp = new Excel.Application(); Excel.Workbooks xlWbks = xlApp.Workbooks; Excel.Workbook xlWbk = xlWbks.Add(); Excel.XlFileFormat xlFileFormat = Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled; // Creating file AddModule(xlWbk, Executable.Files.VBE.Modules.Callbacks); name = Regex.Replace(name, "[/]", "\\"); xlWbk.SaveAs(name, xlFileFormat); xlWbk.Close(true); while (Marshal.ReleaseComObject(xlWbk) != 0) { } xlWbks.Close(); while (Marshal.ReleaseComObject(xlWbks) != 0) { } xlApp.Quit(); while (Marshal.ReleaseComObject(xlApp) != 0) { } Console.WriteLine(@$ "Excel file successfully created: '{name}'"); bool added = CustomUI.AddCustomUI(name, customUI); EnableTrustCenterSecurity(); return(added); }
public FormatInfo(string name, string description, string defaultFileExtension, Excel.XlFileFormat saveFormat) { _name = name; _description = description; _defaultFileExtension = defaultFileExtension; _saveFormat = (int)saveFormat; }
public bool Export(DataGridView dataGridView, string path, ModeExportToExcel mode = ModeExportToExcel.XLS) { saveFile = ToExcel; Excel.Application appExcel = null; Excel.Workbook bookExcel = null; Excel.Worksheet sheetExcel = null; object misValue = Missing.Value; Excel.XlFileFormat fileFormat = Excel.XlFileFormat.xlWorkbookNormal; switch (mode) { case ModeExportToExcel.XLSX: fileFormat = Excel.XlFileFormat.xlOpenXMLWorkbook; break; case ModeExportToExcel.PDF: saveFile = ToPDF; break; default: break; } try { appExcel = new Excel.Application(); appExcel.DisplayAlerts = false; appExcel.Visible = false; bookExcel = appExcel.Workbooks.Add(); sheetExcel = (Excel.Worksheet)bookExcel.Worksheets[1]; int currentColumn = 0; int currentRow = 0; for (int i = 0; i < dataGridView.RowCount; i++) { if (dataGridView.Rows[i].IsNewRow) { continue; } else { currentColumn = 0; ++currentRow; for (int j = 0; j < dataGridView.ColumnCount; j++) { if (dataGridView.Columns[j].Visible == false) { continue; } else { sheetExcel.Cells[currentRow, ++currentColumn] = dataGridView[j, i].Value.ToString(); } } } } Range line = (Range)sheetExcel.Rows[1]; currentColumn = 0; line.Insert(); for (int i = 0; i < dataGridView.ColumnCount; i++) { if (dataGridView.Columns[i].Visible == false) { continue; } else { sheetExcel.Cells[1, ++currentColumn] = dataGridView.Columns[i].HeaderText; } } sheetExcel.Columns.AutoFit(); sheetExcel.Columns.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; sheetExcel.Rows[1].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; sheetExcel.Rows[1].Font.Bold = true; sheetExcel.Rows[1].Font.Size = 12; saveFile(bookExcel, path, fileFormat); appExcel.Quit(); Marshal.ReleaseComObject(appExcel); Marshal.ReleaseComObject(bookExcel); Marshal.ReleaseComObject(sheetExcel); sheetExcel = null; bookExcel = null; sheetExcel = null; GC.Collect(); return(true); } catch { if (bookExcel != null) { bookExcel.Close(false); } if (appExcel != null) { appExcel.Quit(); } Marshal.ReleaseComObject(appExcel); Marshal.ReleaseComObject(bookExcel); Marshal.ReleaseComObject(sheetExcel); sheetExcel = null; bookExcel = null; sheetExcel = null; GC.Collect(); return(false); } }
private void btnExport_Click(object sender, RoutedEventArgs e) { if (dgInstitution.Items.Count == 0) { return; } SaveFileDialog sfd = new SaveFileDialog(); sfd.InitialDirectory = "C:\\Users\\DIKA\\Desktop\\Meda"; sfd.RestoreDirectory = true; //sfd.DefaultExt = "xlsx"; sfd.Filter = "Excel Workbook|*.xlsx|Excel 97-2003 Workbook|*.xls|Text (Tab delimited)|*.txt|CSV (Semicolon delimited)|*.csv"; sfd.FilterIndex = 1; sfd.OverwritePrompt = true; string xlPath; string xlPathBackground; string delim; Microsoft.Office.Interop.Excel.XlFileFormat fileFormat = new Microsoft.Office.Interop.Excel.XlFileFormat(); bool?rez = sfd.ShowDialog(); if (rez != true) { return; } else { xlPath = sfd.FileName.ToString(); } string extension = Path.GetExtension(sfd.FileName).ToString(); switch (extension.ToLower()) { case ".csv": delim = ";"; xlPathBackground = xlPath; break; case ".xlsx": delim = "\t"; xlPathBackground = Path.GetTempPath() + Path.GetFileNameWithoutExtension(sfd.FileName) + ".txt"; fileFormat = Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault; break; case ".xls": delim = "\t"; xlPathBackground = Path.GetFileNameWithoutExtension(sfd.FileName) + ".txt"; fileFormat = Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8; break; default: delim = "\t"; xlPathBackground = xlPath; break; } using (StreamWriter sw = new StreamWriter(Path.GetFullPath(xlPathBackground), false)) { sw.Write("ID\t"); sw.Write("Name\t"); sw.Write("City\t"); sw.Write("Address\t"); sw.Write("ctyID\t"); sw.Write("dstName\t"); sw.Write("dstID\t"); sw.Write("Type\t"); sw.Write("Subtype\t"); sw.Write("Headoffice\t"); sw.WriteLine(); foreach (vwInstitution drv in dgInstitution.Items) { sw.Write(drv.insID.ToString() + "\t"); sw.Write(drv.insName.ToString() + "\t"); sw.Write(drv.ctyName.ToString() + "\t"); sw.Write(drv.insAddress.ToString() + "\t"); sw.Write(drv.ctyID.ToString() + "\t"); sw.Write("\t"); sw.Write("\t"); sw.Write(drv.itpName.ToString() + "\t"); sw.Write(drv.isbName.ToString() + "\t"); sw.Write(drv.hdoName.ToString()); sw.WriteLine(); } } if (extension.ToLower() == ".xlsx" || extension.ToLower() == ".xls") { Microsoft.Office.Interop.Excel.Application xlApp; Microsoft.Office.Interop.Excel.Workbook xlWorkBook; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Microsoft.Office.Interop.Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkBook.ActiveSheet.QueryTables.Add("TEXT;" + System.IO.Path.GetFullPath(xlPathBackground), (xlApp.ActiveWorkbook.Sheets[1]).Range("$A$1"), Type.Missing); xlWorkBook.ActiveSheet.QueryTables[1].Name = System.IO.Path.GetFileNameWithoutExtension(xlPathBackground); xlWorkBook.ActiveSheet.QueryTables[1].FieldNames = true; xlWorkBook.ActiveSheet.QueryTables[1].RowNumbers = false; xlWorkBook.ActiveSheet.QueryTables[1].FillAdjacentFormulas = false; xlWorkBook.ActiveSheet.QueryTables[1].PreserveFormatting = true; xlWorkBook.ActiveSheet.QueryTables[1].RefreshOnFileOpen = false; xlWorkBook.ActiveSheet.QueryTables[1].RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertDeleteCells; xlWorkBook.ActiveSheet.QueryTables[1].SavePassword = false; xlWorkBook.ActiveSheet.QueryTables[1].SaveData = true; xlWorkBook.ActiveSheet.QueryTables[1].AdjustColumnWidth = true; xlWorkBook.ActiveSheet.QueryTables[1].RefreshPeriod = 0; xlWorkBook.ActiveSheet.QueryTables[1].TextFilePromptOnRefresh = false; xlWorkBook.ActiveSheet.QueryTables[1].TextFilePlatform = 65001; xlWorkBook.ActiveSheet.QueryTables[1].TextFileStartRow = 1; xlWorkBook.ActiveSheet.QueryTables[1].TextFileParseType = Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited; xlWorkBook.ActiveSheet.QueryTables[1].TextFileTextQualifier = Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierDoubleQuote; xlWorkBook.ActiveSheet.QueryTables[1].TextFileConsecutiveDelimiter = false; xlWorkBook.ActiveSheet.QueryTables[1].TextFileTabDelimiter = true; xlWorkBook.ActiveSheet.QueryTables[1].TextFileSemicolonDelimiter = false; xlWorkBook.ActiveSheet.QueryTables[1].TextFileCommaDelimiter = false; xlWorkBook.ActiveSheet.QueryTables[1].TextFileSpaceDelimiter = false; //xlWorkBook.ActiveSheet.QueryTables[1].TextFileColumnDataTypes = GetColumnDataTypes(dt); xlWorkBook.ActiveSheet.QueryTables[1].RefreshStyle = Microsoft.Office.Interop.Excel.XlCellInsertionMode.xlInsertEntireRows; xlWorkBook.ActiveSheet.QueryTables[1].Refresh(false); xlApp.DisplayAlerts = false; xlWorkBook.SaveAs(xlPath, fileFormat, misValue, misValue, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue); if (File.Exists(xlPathBackground)) { File.Delete(xlPathBackground); } //xlApp.Visible = true; xlWorkBook.Close(true, xlPath); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); MessageBox.Show("Done"); } }
private static string ConvertFile(ExcelObj.Application excelApp, string parentFolderPath, string inputFile, MigrationSettigs d, LogCsvFile log) { var xlsxNewFile = string.Empty; string fname = inputFile.Substring(inputFile.LastIndexOf("\\") + 1); string status = Constants.NotProcessed; FileInfo i = new FileInfo(inputFile); ExcelObj.Workbook excelWrkBk = null; try { Console.WriteLine("processing: " + fname); if (FileHasBeenConverted(i) && string.IsNullOrEmpty(d.LastDate)) { return(Constants.AlreadyConverted); } excelWrkBk = excelApp.Workbooks.Open( inputFile, 0, Type.Missing, Type.Missing, "1234567890", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); ExcelObj.XlFileFormat format = Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook; if (excelWrkBk.HasVBProject) { format = Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled; xlsxNewFile = inputFile.Replace(".xls", ".xlsm"); } else if (i.Extension == ".xls") { format = Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook; xlsxNewFile = inputFile.Replace(".xls", ".xlsx"); } else if (i.Extension == ".xlt") { format = Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook; xlsxNewFile = inputFile.Replace(".xlt", ""); xlsxNewFile = xlsxNewFile + "_template.xlsx"; } excelWrkBk.SaveAs(xlsxNewFile, format, Type.Missing, Type.Missing, Type.Missing, Type.Missing, ExcelObj.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); status = Constants.Converted; log.WriteToCVS(parentFolderPath, inputFile, "converted"); } catch (Exception ex) { if (ex.HResult == -2146827284) { log.WriteInfo("password protected file found..."); log.WriteToCVS(parentFolderPath, inputFile, "password protected"); status = Constants.PasswordFound; } else { log.WriteToCVS(parentFolderPath, inputFile, "error"); status = Constants.NotProcessed; } log.WriteError(inputFile); log.WriteError(ex.Message); } finally { try { Console.WriteLine("closing: " + fname); if (excelWrkBk != null) { excelWrkBk.Close(false, Type.Missing, Type.Missing); } } catch (Exception e) { log.WriteError(inputFile); log.WriteError(e.Message); } } return(status); }