/// <summary>
/// Saves the document out with the appropriate new filename and format.
/// Also does any special handling such as accepting/rejecting changes
/// before saving.
/// </summary>
private void SaveDocument()
{
HandleTrackedChanges();
object missing = System.Reflection.Missing.Value;
object newFileName = m_newFileName;
Excel_Class.XlSaveAsAccessMode accessMode = Excel_Class.XlSaveAsAccessMode.xlNoChange;
object conflictResolution = Excel_Class.XlSaveConflictResolution.xlOtherSessionChanges;
if (m_newFormatType.Equals(Excel_Class.XlFixedFormatType.xlTypePDF))
{
m_workbook.ExportAsFixedFormat(Excel_Class.XlFixedFormatType.xlTypePDF, m_newFileName, missing,
missing, missing, missing, missing,
missing, missing);
}
else
{
m_workbook.SaveAs(m_newFileName, m_newFormatType, missing,
missing, missing, missing, accessMode,
conflictResolution, missing, missing, missing, missing);
}
object saveChanges = false;
object routeWb = false;
m_workbook.Close(saveChanges, missing, routeWb);
m_workbook = null;
}
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 PopulateAndMove_DT_BlankAssy(clsFiles Files_In, clsProject Project_In,
clsDB DB_In, string FilePath_In)
//=================================================================================
{
try
{
EXCEL.Application pApp = null;
pApp = new EXCEL.Application();
//....Open Original WorkBook.
EXCEL.Workbook pWkbOrg = null;
pWkbOrg = pApp.Workbooks.Open(Files_In.FileTitle_Template_EXCEL_BlankAssy, mobjMissing, false,
mobjMissing, mobjMissing, mobjMissing, mobjMissing,
mobjMissing, mobjMissing, mobjMissing, mobjMissing,
mobjMissing, mobjMissing, mobjMissing, mobjMissing);
//....Open 'Sketchs' WorkSheets.
EXCEL.Worksheet pWkSheet = (EXCEL.Worksheet)pWkbOrg.Sheets[1];
//....Set Project Number.
int pNo_Suffix = 1;
if (Project_In.AssyDwg.No_Suffix != "")
{
pNo_Suffix = Convert.ToInt32(Project_In.AssyDwg.No_Suffix);
}
if (pNo_Suffix <= 9)
{
pWkSheet.Cells[3, 1] = Project_In.AssyDwg.No + "-0" + (pNo_Suffix + 1).ToString();
pWkSheet.Cells[4, 1] = Project_In.AssyDwg.No + "-0" + (pNo_Suffix + 1).ToString() + "T";
pWkSheet.Cells[5, 1] = Project_In.AssyDwg.No + "-0" + (pNo_Suffix + 4).ToString();
}
else
{
pWkSheet.Cells[3, 1] = Project_In.AssyDwg.No + "-" + (pNo_Suffix + 1).ToString();
pWkSheet.Cells[4, 1] = Project_In.AssyDwg.No + "-" + (pNo_Suffix + 1).ToString() + "T";
pWkSheet.Cells[5, 1] = Project_In.AssyDwg.No + "-" + (pNo_Suffix + 4).ToString();
}
//....Set Value.
StringCollection pCellColName = new StringCollection();
StringCollection pSoftware_VarVal = new StringCollection();
DB_In.PopulateStringCol(pCellColName, "tblMapping_Blank", "fldCellColName", "");
DB_In.PopulateStringCol(pSoftware_VarVal, "tblMapping_Blank", "fldSoftware_VarVal", "");
for (int i = 0; i < pCellColName.Count; i++)
{
for (int j = 3; j < 6; j++)
{
if (pSoftware_VarVal[i] != "")
{
int pIndx = ColumnNumber(pCellColName[i]);
pWkSheet.Cells[j, pIndx] = pSoftware_VarVal[i];
}
}
}
string pFile = modMain.ExtractPreData(mcBlankAssy_Title, ".");
String pFileName = FilePath_In + "\\" + pFile + ".xlsx";
if (!Directory.Exists(FilePath_In))
{
Directory.CreateDirectory(FilePath_In);
}
if (File.Exists(pFileName))
{
File.Delete(pFileName);
}
EXCEL.XlSaveAsAccessMode pAccessMode = Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive;
pWkbOrg.SaveAs(pFileName, pWkbOrg.FileFormat, mobjMissing,
mobjMissing, false, mobjMissing, pAccessMode,
mobjMissing, mobjMissing, mobjMissing,
mobjMissing, mobjMissing);
//pApp.Visible = true;
if (FilePath_In.Contains("Ref. Files"))
{
pApp.Visible = false;
}
else
{
pApp.Visible = true;
}
}
catch (Exception pEXP)
{
}
}
public static string Createworddocument(List <DagsRegistrering> regs, string fileName)
{
string path = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), @"Templates\");
string baseFolderPath = System.IO.Path.GetDirectoryName(Assembly.GetEntryAssembly().Location);
string templateFilePath = System.IO.Path.Combine(path, fileName + ".xlsx");
MSExcel.Application ExcelApp = new MSExcel.Application();
MSExcel.Workbook xlWorkBook;
MSExcel.Worksheet xlWorkSheet;
object missing = System.Reflection.Missing.Value;
if (File.Exists((string)templateFilePath) && regs.Count != 0)
{
try
{
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode AccessMode = Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange;
object readOnly = false;
object isvisible = false;
ExcelApp.Visible = false;
xlWorkBook = ExcelApp.Workbooks.Open(templateFilePath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkBook.Activate();
xlWorkSheet = xlWorkBook.Worksheets[1];
xlWorkSheet.Cells[2, 1] = "Navn: " + regs[0].ElevNavn;
xlWorkSheet.Cells[3, 6] = Util.ConvertPeriode(regs[0].Dato);
int row = setStartRow(regs[0].Dato);
int Month = regs[0].Dato.Month;
for (int i = 0; i < regs.Count; i++)
{
//fyld skema
xlWorkSheet.Cells[row, 2] = Util.convertWeekDay(regs[i].Dato);
xlWorkSheet.Cells[row, 3] = "09:00";
xlWorkSheet.Cells[row, 4] = "14:00";
if (regs[i].Sygdom == 5)
{
xlWorkSheet.Cells[row, 6] = 5;
}
else if (regs[i].LovligFraværd == 5)
{
xlWorkSheet.Cells[row, 8] = "LM Skriv";
}
int tmp = row;
row = calcNextRow(row, regs[i].Dato);
if (regs[i].Dato.AddDays(1).Month == Month && row - 4 == tmp)
{
xlWorkSheet.Cells[tmp + 1, 2] = "Fredag";
xlWorkSheet.Cells[tmp + 1, 8] = "pædagogisk dag";
}
else if (i + 1 < regs.Count)
{
int FerieDage = calcFrerie(regs[i].Dato, regs[i + 1].Dato);
if (FerieDage > 0)
{
row = tmp + 1;
}
for (int j = 1; j < FerieDage; j++)
{
if (regs[i].Dato.AddDays(j).DayOfWeek != DayOfWeek.Saturday && regs[i].Dato.AddDays(j).DayOfWeek != DayOfWeek.Sunday)
{
xlWorkSheet.Cells[row, 8] = "Ferie";
xlWorkSheet.Cells[row, 2] = Util.convertWeekDay(regs[i].Dato.AddDays(j));
}
row += 1;
}
}
}
string newfileName = "fraværdStruerKommune" + regs[0].ElevNavn + "-" + DateTime.Now.ToString().Replace(":", "-") + ".xlsx";
object saveas = System.IO.Path.Combine(baseFolderPath, newfileName);
xlWorkBook.SaveAs(saveas, missing, missing, missing
, missing, missing, AccessMode, missing
, missing, missing, missing, missing);
xlWorkBook.Close();
ExcelApp.Quit();
return(saveas.ToString());
}
catch (Exception ex)
{
return("");
}
}
else
{
return("");
}
}
private void Populate_EXCEL_TB_Assy(clsFiles Files_In, clsProject Project_In,
clsDB DB_In, string FileName_In)
//============================================================================
{
try
{
EXCEL.Application pApp = null;
pApp = new EXCEL.Application();
//....Open Original WorkBook.
EXCEL.Workbook pWkbOrg = null;
pWkbOrg = pApp.Workbooks.Open(Files_In.FileTitle_Template_EXCEL_TB_Assy, mobjMissing, false,
mobjMissing, mobjMissing, mobjMissing, mobjMissing,
mobjMissing, mobjMissing, mobjMissing, mobjMissing,
mobjMissing, mobjMissing, mobjMissing, mobjMissing);
//....Open 'Sketchs' WorkSheets.
EXCEL.Worksheet pWkSheet = (EXCEL.Worksheet)pWkbOrg.Sheets[1];
//....Set Project Number.
int pRowBegin = 3;
int pJCount = 6;
bool pTL_TB_Both = false;
int pNo_Suffix = 1;
if (Project_In.AssyDwg.No_Suffix != "")
{
pNo_Suffix = Convert.ToInt32(Project_In.AssyDwg.No_Suffix);
}
if (Project_In.Product.EndConfig[0].Type == clsEndConfig.eType.Thrust_Bearing_TL &&
Project_In.Product.EndConfig[1].Type == clsEndConfig.eType.Thrust_Bearing_TL)
{
if (pNo_Suffix <= 9)
{
pWkSheet.Cells[pRowBegin, 1] = Project_In.AssyDwg.No + "-0" + (pNo_Suffix + 2).ToString();
pWkSheet.Cells[pRowBegin + 1, 1] = Project_In.AssyDwg.No + "-0" + (pNo_Suffix + 2).ToString() + "D";
pWkSheet.Cells[pRowBegin + 2, 1] = Project_In.AssyDwg.No + "-0" + (pNo_Suffix + 5).ToString();
pWkSheet.Cells[pRowBegin + 3, 1] = Project_In.AssyDwg.No + "-0" + (pNo_Suffix + 3).ToString();
pWkSheet.Cells[pRowBegin + 4, 1] = Project_In.AssyDwg.No + "-0" + (pNo_Suffix + 3).ToString() + "D";
pWkSheet.Cells[pRowBegin + 5, 1] = Project_In.AssyDwg.No + "-0" + (pNo_Suffix + 6).ToString();
}
else
{
pWkSheet.Cells[pRowBegin, 1] = Project_In.AssyDwg.No + (pNo_Suffix + 2).ToString();
pWkSheet.Cells[pRowBegin + 1, 1] = Project_In.AssyDwg.No + (pNo_Suffix + 2).ToString() + "D";
pWkSheet.Cells[pRowBegin + 2, 1] = Project_In.AssyDwg.No + (pNo_Suffix + 5).ToString();
pWkSheet.Cells[pRowBegin + 3, 1] = Project_In.AssyDwg.No + (pNo_Suffix + 3).ToString();
pWkSheet.Cells[pRowBegin + 4, 1] = Project_In.AssyDwg.No + (pNo_Suffix + 3).ToString() + "D";
pWkSheet.Cells[pRowBegin + 5, 1] = Project_In.AssyDwg.No + (pNo_Suffix + 6).ToString();
}
pJCount = 9;
pTL_TB_Both = true;
}
else
{
if (Project_In.Product.EndConfig[0].Type == clsEndConfig.eType.Thrust_Bearing_TL)
{
if (pNo_Suffix <= 9)
{
pWkSheet.Cells[pRowBegin, 1] = Project_In.AssyDwg.No + "-0" + (pNo_Suffix + 2).ToString();
pWkSheet.Cells[pRowBegin + 1, 1] = Project_In.AssyDwg.No + "-0" + (pNo_Suffix + 2).ToString() + "D";
pWkSheet.Cells[pRowBegin + 2, 1] = Project_In.AssyDwg.No + "-0" + (pNo_Suffix + 5).ToString();
}
else
{
pWkSheet.Cells[pRowBegin, 1] = Project_In.AssyDwg.No + (pNo_Suffix + 2).ToString();
pWkSheet.Cells[pRowBegin + 1, 1] = Project_In.AssyDwg.No + (pNo_Suffix + 2).ToString() + "D";
pWkSheet.Cells[pRowBegin + 2, 1] = Project_In.AssyDwg.No + (pNo_Suffix + 5).ToString();
}
pRowBegin++;
}
if (Project_In.Product.EndConfig[1].Type == clsEndConfig.eType.Thrust_Bearing_TL)
{
if (pNo_Suffix <= 9)
{
pWkSheet.Cells[pRowBegin, 1] = Project_In.AssyDwg.No + "-0" + (pNo_Suffix + 3).ToString();
pWkSheet.Cells[pRowBegin + 1, 1] = Project_In.AssyDwg.No + "-0" + (pNo_Suffix + 3).ToString() + "D";
pWkSheet.Cells[pRowBegin + 2, 1] = Project_In.AssyDwg.No + "-0" + (pNo_Suffix + 5).ToString();
}
else
{
pWkSheet.Cells[pRowBegin, 1] = Project_In.AssyDwg.No + (pNo_Suffix + 3).ToString();
pWkSheet.Cells[pRowBegin + 1, 1] = Project_In.AssyDwg.No + (pNo_Suffix + 3).ToString() + "D";
pWkSheet.Cells[pRowBegin + 2, 1] = Project_In.AssyDwg.No + (pNo_Suffix + 5).ToString();
}
}
}
//.....Set Value.
StringCollection pCellColName = new StringCollection();
StringCollection pSoftware_Var_Val = new StringCollection();
DB_In.PopulateStringCol(pCellColName, "tblMapping_TB_Assy", "fldCellColName", "");
DB_In.PopulateStringCol(pSoftware_Var_Val, "tblMapping_TB_Assy", "fldSoftware_VarVal", "");
for (int i = 0; i < pCellColName.Count; i++)
{
for (int j = 3; j < pJCount; j++)
{
if (pSoftware_Var_Val[i] != "")
{
int pIndx = ColumnNumber(pCellColName[i]);
string pSoftware_Val;
if (Project_In.Product.EndConfig[0].Type == clsEndConfig.eType.Thrust_Bearing_TL)
{
if (pTL_TB_Both)
{
if (i == 0 && j == 7)
{
//pWkSheet.Cells[j, pIndx] = "B";
}
else
{
pSoftware_Val = modMain.ExtractPreData(pSoftware_Var_Val[i], ",");
pWkSheet.Cells[j, pIndx] = pSoftware_Val;
}
}
else
{
if (i == 0 && j == 5)
{
//pWkSheet.Cells[j, pIndx] = "B";
}
else
{
pSoftware_Val = modMain.ExtractPreData(pSoftware_Var_Val[i], ",");
pWkSheet.Cells[j, pIndx] = pSoftware_Val;
}
}
}
if (Project_In.Product.EndConfig[1].Type == clsEndConfig.eType.Thrust_Bearing_TL)
{
if (pTL_TB_Both)
{
if (i == 0 && j == 8)
{
//pWkSheet.Cells[j, pIndx] = "B";
}
else
{
pSoftware_Val = modMain.ExtractPostData(pSoftware_Var_Val[i], ",");
pWkSheet.Cells[j, pIndx] = pSoftware_Val;
}
}
else
{
if (i == 0 && j == 5)
{
//pWkSheet.Cells[j, pIndx] = "B";
}
else
{
pSoftware_Val = modMain.ExtractPostData(pSoftware_Var_Val[i], ",");
pWkSheet.Cells[j, pIndx] = pSoftware_Val;
}
}
}
}
}
}
String pFileName = FileName_In + "\\" + mcTB_Assy_Title + ".xlsx";
if (!Directory.Exists(FileName_In))
{
Directory.CreateDirectory(FileName_In);
}
if (File.Exists(pFileName))
{
File.Delete(pFileName);
}
EXCEL.XlSaveAsAccessMode pAccessMode = Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive;
pWkbOrg.SaveAs(pFileName, pWkbOrg.FileFormat, mobjMissing,
mobjMissing, false, mobjMissing, pAccessMode,
mobjMissing, mobjMissing, mobjMissing,
mobjMissing, mobjMissing);
pApp.Visible = true;
//}
}
catch (Exception pEXP)
{
MessageBox.Show("Excel File Error - " + pEXP.Message);
}
}
public override void Convert(string inputFilePath, FormatInfo format, string outputFilePath, string password)
{
base.Convert(inputFilePath, format, outputFilePath, password);
MsoAutomationSecurity originalAutomationSecurity = this._application.AutomationSecurity;
this._application.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityForceDisable;
this._application.DisplayAlerts = false;
string tempFilePath = Converter.GetTempFileName(".xlsx");
if (this.Options.UseAddin && Path.GetExtension(inputFilePath) == ".ods")
{
// convert ods to xlsx
this.ConvertWithOdfConverter(inputFilePath, tempFilePath);
inputFilePath = tempFilePath;
}
object writeResPassword = Type.Missing;
object addToMru = false;
string sourceFileName = inputFilePath;
object updateLinks = Type.Missing;
object openReadOnly = true;
object openFormat = Type.Missing;
object openPassword = password ?? Type.Missing;
object ignoreReadOnlyRecommended = Type.Missing;
object origin = Type.Missing;
object delimiter = Type.Missing;
object editable = Type.Missing;
object notify = Type.Missing;
object converter = Type.Missing; // 0
object localDataStoreSlot = Type.Missing;
object corruptLoad = Type.Missing;
Excel.Workbook workbook = this._application.Workbooks.Open(sourceFileName, updateLinks, openReadOnly, openFormat,
openPassword, writeResPassword, ignoreReadOnlyRecommended, origin, delimiter, editable,
notify, converter, addToMru, localDataStoreSlot, corruptLoad);
object targetFileName = outputFilePath;
object saveFormat = format.SaveFormat;
object savePassword = password ?? Type.Missing;
object readOnlyRecommended = Type.Missing;
object createBackup = Type.Missing;
Excel.XlSaveAsAccessMode accessMode = Excel.XlSaveAsAccessMode.xlNoChange;
object conflictResolution = Type.Missing;
object textCodepage = Type.Missing;
object textVisualLayout = Type.Missing;
object local = Type.Missing;
object saveChanges = false;
object filename = Type.Missing;
object routeWorkbook = true;
string tempFilePath2 = Converter.GetTempFileName(".xlsx");
if (this.Options.UseAddin && format.SaveFormat == (int)Excel.XlFileFormat.xlOpenDocumentSpreadsheet)
{
// export to ods using addin
saveFormat = Excel.XlFileFormat.xlOpenXMLWorkbook;
object tempOpenXmlDocument = tempFilePath2;
workbook.SaveAs(tempOpenXmlDocument, saveFormat, savePassword, writeResPassword, readOnlyRecommended, createBackup, accessMode, conflictResolution, addToMru, textCodepage, textVisualLayout, local);
workbook.Close(saveChanges, filename, routeWorkbook);
this.ConvertWithOdfConverter(tempFilePath2, outputFilePath);
}
else if (format.SaveFormat != ExcelPdfFormat && format.SaveFormat != ExcelXpsFormat)
{
workbook.SaveAs(targetFileName, saveFormat, savePassword, writeResPassword, readOnlyRecommended, createBackup, accessMode, conflictResolution, addToMru, textCodepage, textVisualLayout, local);
workbook.Close(saveChanges, filename, routeWorkbook);
}
else
{
Excel.XlFixedFormatType type;
switch (format.SaveFormat)
{
case ExcelXpsFormat:
type = Excel.XlFixedFormatType.xlTypeXPS;
break;
default:
type = Excel.XlFixedFormatType.xlTypePDF;
break;
}
object quality = Type.Missing;
object includeDocProperties = Type.Missing;
object ignorePrintAreas = Type.Missing;
object from = Type.Missing;
object to = Type.Missing;
object openAfterPublish = Type.Missing;
object fixedFormatExtClassPtr = Type.Missing;
workbook.ExportAsFixedFormat(type, targetFileName, quality, includeDocProperties, ignorePrintAreas, from, to, openAfterPublish, fixedFormatExtClassPtr);
workbook.Close(saveChanges, filename, routeWorkbook);
}
if (File.Exists(tempFilePath))
{
File.Delete(tempFilePath);
}
if (File.Exists(tempFilePath2))
{
File.Delete(tempFilePath2);
}
this._application.DisplayAlerts = true;
this._application.AutomationSecurity = originalAutomationSecurity;
}
private void Populate_EXCEL_CompleteAssy(clsFiles Files_In, clsProject Project_In,
clsDB DB_In, string FileName_In)
//================================================================================
{
try
{
EXCEL.Application pApp = null;
pApp = new EXCEL.Application();
//....Open Original WorkBook.
EXCEL.Workbook pWkbOrg = null;
pWkbOrg = pApp.Workbooks.Open(Files_In.FileTitle_Template_EXCEL_CompleteAssy, mobjMissing, false,
mobjMissing, mobjMissing, mobjMissing, mobjMissing,
mobjMissing, mobjMissing, mobjMissing, mobjMissing,
mobjMissing, mobjMissing, mobjMissing, mobjMissing);
//....Open 'Sketchs' WorkSheets.
EXCEL.Worksheet pWkSheet = (EXCEL.Worksheet)pWkbOrg.Sheets[1];
//....Set Value.
StringCollection pCellColName = new StringCollection();
StringCollection pSoftware_VarVal = new StringCollection();
DB_In.PopulateStringCol(pCellColName, "tblMapping_Complete", "fldCellColName", "");
DB_In.PopulateStringCol(pSoftware_VarVal, "tblMapping_Complete", "fldSoftware_VarVal", "");
for (int i = 0; i < pCellColName.Count; i++)
{
for (int j = 3; j < 4; j++)
{
if (pSoftware_VarVal[i] != "")
{
int pIndx = ColumnNumber(pCellColName[i]);
pWkSheet.Cells[j, pIndx] = pSoftware_VarVal[i];
}
}
}
String pFileName = FileName_In + "\\" + mcCompleteAssy_Title + ".xlsx";
if (!Directory.Exists(FileName_In))
{
Directory.CreateDirectory(FileName_In);
}
if (File.Exists(pFileName))
{
File.Delete(pFileName);
}
EXCEL.XlSaveAsAccessMode pAccessMode = Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive;
pWkbOrg.SaveAs(pFileName, pWkbOrg.FileFormat, mobjMissing,
mobjMissing, false, mobjMissing, pAccessMode,
mobjMissing, mobjMissing, mobjMissing,
mobjMissing, mobjMissing);
pApp.Visible = true;
}
catch (Exception pEXP)
{
}
}
public override void Convert(string inputFilePath, FormatInfo format, string outputFilePath, string password)
{
base.Convert(inputFilePath, format, outputFilePath, password);
MsoAutomationSecurity originalAutomationSecurity = this._application.AutomationSecurity;
this._application.AutomationSecurity = MsoAutomationSecurity.msoAutomationSecurityForceDisable;
object writeResPassword = Type.Missing;
object addToMru = false;
string sourceFileName = inputFilePath;
object updateLinks = Type.Missing;
object openReadOnly = true;
object openFormat = Type.Missing;
object openPassword = password ?? Type.Missing;
object ignoreReadOnlyRecommended = Type.Missing;
object origin = Type.Missing;
object delimiter = Type.Missing;
object editable = Type.Missing;
object notify = Type.Missing;
object Converter = Type.Missing; // 0
object LocalDataStoreSlot = Type.Missing;
object corruptLoad = Type.Missing;
Excel.Workbook workbook = this._application.Workbooks.Open(sourceFileName, updateLinks, openReadOnly, openFormat, openPassword, writeResPassword, ignoreReadOnlyRecommended, origin, delimiter, editable, notify, Converter, addToMru, LocalDataStoreSlot, corruptLoad);
if (format.SaveFormat != ExcelPdfFormat && format.SaveFormat != ExcelXpsFormat)
{
object targetFileName = outputFilePath;
object saveFormat = format.SaveFormat;
object savePassword = password ?? Type.Missing;
object readOnlyRecommended = Type.Missing;
object createBackup = Type.Missing;
Excel.XlSaveAsAccessMode accessMode = Excel.XlSaveAsAccessMode.xlNoChange;
object conflictResolution = Type.Missing;
object textCodepage = Type.Missing;
object textVisualLayout = Type.Missing;
object local = Type.Missing;
workbook.SaveAs(targetFileName, saveFormat, savePassword, writeResPassword, readOnlyRecommended, createBackup, accessMode, conflictResolution, addToMru, textCodepage, textVisualLayout, local);
}
else
{
Excel.XlFixedFormatType type;
switch (format.SaveFormat)
{
case ExcelXpsFormat:
type = Excel.XlFixedFormatType.xlTypeXPS;
break;
default:
type = Excel.XlFixedFormatType.xlTypePDF;
break;
}
object targetFileName = outputFilePath;
object quality = Type.Missing;
object includeDocProperties = Type.Missing;
object ignorePrintAreas = Type.Missing;
object from = Type.Missing;
object to = Type.Missing;
object openAfterPublish = Type.Missing;
object fixedFormatExtClassPtr = Type.Missing;
workbook.ExportAsFixedFormat(type, targetFileName, quality, includeDocProperties, ignorePrintAreas, from, to, openAfterPublish, fixedFormatExtClassPtr);
}
object saveChanges = false;
object filename = Type.Missing;
object routeWorkbook = true;
workbook.Close(saveChanges, filename, routeWorkbook);
this._application.AutomationSecurity = originalAutomationSecurity;
}
private void timer1_Tick(object sender, EventArgs e)
{
if (y == 0)
{
label1.Text = "Loading...";
}
else if (y == 10)
{
label1.Text = "Loading.";
}
else if (y == 20)
{
label1.Text = "Loading..";
}
else if (y == 30)
{
label1.Text = "Loading...";
}
else if (y == 40)
{
label1.Text = "Loading.";
}
else if (y == 50)
{
label1.Text = "Loading..";
y = -10;
}
y = y + 10;
if (x == 0)
{
System.Threading.Thread.Sleep(1000);
x = 2;
}
if (File.Exists(path) == true && File.Exists(path2))
{
//do nothing everything should be in place
Console.WriteLine("File exists, proceeding");
//TESTING
}
else
{
label1.Text = "Preforming First Time Setup...";
Console.WriteLine("File does not exist, writing file...");
//directory may not exit
if (System.IO.Directory.Exists(direct) == true)
{
//directory exists
Console.WriteLine("Directory exists");
}
else
{
//directory does not exist
Console.WriteLine("Directory does not exist, creating...");
System.IO.Directory.CreateDirectory(direct);
Console.WriteLine("Directory has been created");
}
//System.IO.File.Create("C:\\fblaManager\\fblaDatabase.xls"); DOES NOT WORK
Excel.Application startup = new Excel.Application();
startup.Visible = false;
startup.DisplayAlerts = false;
Excel.Workbooks startWorkbooks;
Excel.Workbook startWorkbook;
Excel.Worksheet startWorksheet;
object misValue2 = System.Reflection.Missing.Value;
startWorkbooks = startup.Workbooks;
startWorkbook = startup.Workbooks.Add(misValue2);
Excel.XlSaveAsAccessMode save = Excel.XlSaveAsAccessMode.xlNoChange;
startWorkbook.SaveAs("C:\\fblaManager\\fblaDatabase.xls", misValue2, "lambda3b", misValue2, misValue2, misValue2, save, misValue2, misValue2, misValue2, misValue2, misValue2);
startWorkbook.SaveAs("C:\\fblaManager\\fblaLogins.xls", misValue2, "lambda3b", misValue2, misValue2, misValue2, save, misValue2, misValue2, misValue2, misValue2, misValue2);
startWorksheet = (Excel.Worksheet)startWorkbook.ActiveSheet;
startWorksheet.Name = "fblaData";
startWorkbook.Save();
startWorkbook.Close();
startup.Quit();
Console.WriteLine("Were good to go");
progressBar1.Increment(9);
//setting up file
Console.WriteLine("Testing write");
Excel.Application myExcelApp;
Excel.Workbooks myExcelWorkbooks;
Excel.Workbook myExcelWorkbook;
object misValue = System.Reflection.Missing.Value;
Console.WriteLine("stuff has been created");
myExcelApp = new Excel.Application();
myExcelApp.Visible = false;
myExcelWorkbooks = myExcelApp.Workbooks;
Console.WriteLine("More stuff has been created lljlkjlj");
string fileName = "C:\\fblaManager\\fblaDatabase.xls";
//setting file information
myExcelWorkbook = myExcelWorkbooks.Open(fileName, misValue, misValue, misValue, "lambda3b", misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue, misValue);
Excel.Worksheet myExcelWorksheet = (Excel.Worksheet)myExcelWorkbook.ActiveSheet;
Console.WriteLine("whoa even more stuff)");
Console.WriteLine("All Done part 1");
//writing to file
myExcelWorksheet.get_Range("A1", misValue).Formula = "MemberNumber";
myExcelWorksheet.get_Range("B1", misValue).Formula = "First";
myExcelWorksheet.get_Range("C1", misValue).Formula = "Last";
myExcelWorksheet.get_Range("D1", misValue).Formula = "School";
myExcelWorksheet.get_Range("E1", misValue).Formula = "Grade";
myExcelWorksheet.get_Range("F1", misValue).Formula = "State";
myExcelWorksheet.get_Range("G1", misValue).Formula = "Email";
myExcelWorksheet.get_Range("H1", misValue).Formula = "YearJoined";
myExcelWorksheet.get_Range("I1", misValue).Formula = "Active";
myExcelWorksheet.get_Range("J1", misValue).Formula = "MoneyOwed";
//this changes the cell value in C2 to "New Value";
myExcelWorkbook.Save();
myExcelWorkbook.Close();
Console.WriteLine("All Done :)");
myExcelApp.Quit();
/* FOR READING
* string cellFormulaAsString = myExcelWorksheet.get_Range("A2", misValue).Formula.ToString();
* //this puts the formula in Cell A2 or text depending whats in it in the string.
*/
}
progressBar1.Increment(3);
if (progressBar1.Value == 100)
{
System.Threading.Thread.Sleep(1000);
this.Visible = false;
login f2 = new login();
f2.Show();
timer1.Stop();
}
}
static void Main(string[] args)
{
Excel.Application excelApp = null;
Excel.Worksheet xlSheet;
Excel.Workbook xlBook;
try
{
excelApp = new Excel.Application();
string templatePath = @"..\..\ExcelTemplate\FTPLogReport.xltx";
string CSVPath = @"..\..\Input\08Oct2013_FTPLogReport.csv";
string OutputPath = @"..\..\Output\08Oct2013_FTPLogReport.xlsx";
templatePath = Path.GetFullPath(templatePath);
CSVPath = Path.GetFullPath(CSVPath);
OutputPath = Path.GetFullPath(OutputPath);
xlBook = (Excel.Workbook)excelApp.Workbooks.Open(templatePath, 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);
int row = 2;
DataTable dtCSV = CsvFileToDatatable(CSVPath, true);
foreach (DataRow dr in dtCSV.Rows)
{
excelApp.Cells[row, 1] = dr[0].ToString();
excelApp.Cells[row, 2] = dr[1].ToString();
excelApp.Cells[row, 3] = dr[2].ToString();
excelApp.Cells[row, 4] = dr[3].ToString();
excelApp.Cells[row, 5] = dr[4].ToString();
row++;
}
xlSheet = (Excel.Worksheet)xlBook.Worksheets.get_Item(1);
xlSheet.Name = "08Oct2013";
object oFilename = OutputPath;
object oFileFormat = Excel.XlFileFormat.xlWorkbookDefault;
object oPassword = Missing.Value;
object oWriteResPassword = Missing.Value;
object oReadOnlyRecommended = false;
object oCreateBackup = false;
Excel.XlSaveAsAccessMode AccessMode = Excel.XlSaveAsAccessMode.xlNoChange;
object oConflictResolution = false;
object oAddToMru = true;
object oTextCodepage = Missing.Value;
object oTextVisualLayout = Missing.Value;
object oSaveChanges = true;
object oRouteWorkbook = Missing.Value;
excelApp.DisplayAlerts = false;
xlBook.SaveAs(oFilename, oFileFormat, oPassword, oWriteResPassword, oReadOnlyRecommended, oCreateBackup, AccessMode, oConflictResolution, oAddToMru, oTextCodepage, oTextVisualLayout);
xlBook.Close(oSaveChanges, oFilename, oRouteWorkbook);
}
catch (Exception ex)
{
throw ex;
}
finally
{
System.Threading.Thread.Sleep(1000);
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
GC.Collect();
GC.WaitForPendingFinalizers();
System.Threading.Thread.Sleep(30000);
}
}
