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);
}
