public ExportResult DoExport(string fileName, int userId, ExportType exportType) { try { System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); excel.Application xlsApp = new excel.ApplicationClass(); xlsApp.DisplayAlerts = false; excel.Workbook xlsWorkbook; excel.Worksheet xlsWorksheet; excel.Range rng = null; object missing = System.Reflection.Missing.Value; xlsWorkbook = xlsApp.Workbooks.Open(Path.Combine(Environment.CurrentDirectory, @"Exports\WHO_EPIRF_PC_NATDAT.xls"), missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); Country country = demo.GetCountry(); int reportYear = DateTime.Now.Year; xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[1]; AddInfo(xlsWorksheet, rng, country, exportType, ref reportYear); //xlsApp.Visible = true; //set to 'true' when debbugging, Exec is visible //xlsApp.DisplayAlerts = true; //enable all the prompt alerts for debug. DateTime start = new DateTime(reportYear, 1, 1); DateTime end = new DateTime(reportYear, 12, 31); xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[2]; AddLfMm(xlsWorksheet, start, end); xlsApp.Run("Sheet13.UNIT_LF"); xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[3]; AddOncho(xlsWorksheet, start, end); xlsApp.Run("Sheet17.UNIT_ONCHO"); xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[4]; AddSth(xlsWorksheet, start, end); xlsApp.Run("Sheet15.UNIT_STH"); xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[5]; AddSch(xlsWorksheet, start, end); xlsApp.Run("Sheet16.UNIT_SCH"); xlsWorkbook.SaveAs(fileName, excel.XlFileFormat.xlOpenXMLWorkbook, missing, missing, false, false, excel.XlSaveAsAccessMode.xlNoChange, excel.XlSaveConflictResolution.xlUserResolution, true, missing, missing, missing); xlsApp.ScreenUpdating = true; xlsApp.Visible = true; rng = null; Marshal.ReleaseComObject(xlsWorksheet); Marshal.ReleaseComObject(xlsWorkbook); Marshal.ReleaseComObject(xlsApp); System.Threading.Thread.CurrentThread.CurrentCulture = oldCI; return new ExportResult { WasSuccess = true }; } catch (Exception ex) { return new ExportResult(ex.Message); } }
/// <summary> /// https://stackoverflow.com/questions/14248592/running-an-excel-macro-via-c-run-a-macro-from-one-workbook-on-another /// </summary> public static void openExcel() { // Object for missing (or optional) arguments. object oMissing = System.Reflection.Missing.Value; // Create an instance of Microsoft Excel Excel.ApplicationClass oExcel = new Excel.ApplicationClass(); // Make it visible oExcel.Visible = true; // Open Worksheet01.xlsm Excel.Workbooks oBooks = oExcel.Workbooks; Excel._Workbook oBook = null; oBook = oBooks.Open("C:\\devel\\warehouse\\WarehouseDatabase.xlsm", oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing); ((Excel.Worksheet)oExcel.ActiveWorkbook.Sheets[7]).Select(); Excel._Worksheet workSheet = (Excel.Worksheet)oExcel.ActiveSheet; workSheet.Cells[1, "B"] = "70007926259"; // "Product value"; workSheet.Cells[2, "B"] = "1"; //"Pallet value"; workSheet.Cells[2, "F"] = "071309-WH"; //"Location value"; //oExcel.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null, oExcel, new object[] { "C:\\devel\\warehouse\\WarehouseDatabase.xlsm!SearchIn" }); oExcel.Run("SearchIn", false); }
public ExportResult ExportData(string filePath, int userId, ExportJrfQuestions questions) { try { int yearReported = questions.JrfYearReporting.Value; System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); excel.Application xlsApp = new excel.ApplicationClass(); excel.Workbook xlsWorkbook; excel.Worksheet xlsWorksheet; excel.Worksheet xlsSummary; excel.Range rng = null; object missing = System.Reflection.Missing.Value; // Open workbook xlsWorkbook = xlsApp.Workbooks.Open(Path.Combine(Environment.CurrentDirectory, @"Exports\WHO_JRF_PC.xls"), missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); var districtLevel = questions.AdminLevelType; CountryDemography countryStats = demo.GetCountryLevelStatsRecent(); Country country = demo.GetCountry(); List<AdminLevel> demography = new List<AdminLevel>(); DateTime startDate = new DateTime(yearReported, 1, 1); DateTime endDate = startDate.AddYears(1).AddDays(-1); List<AdminLevel> tree = demo.GetAdminLevelTreeForDemography(districtLevel.LevelNumber, startDate, endDate, ref demography); var reportingLevelUnits = demography.Where(d => d.LevelNumber == districtLevel.LevelNumber).ToList(); Dictionary<int, DataRow> aggIntvs = GetIntvsAggregatedToReportingLevel(startDate, endDate, reportingLevelUnits); // Info page xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[1]; xlsWorksheet.Unprotect(); AddQuestions(xlsWorksheet, rng, questions, countryStats, demography, districtLevel.LevelNumber, country); // run macro to create district rows. xlsApp.DisplayAlerts = false; xlsApp.Run("Sheet1.DISTRICT"); xlsWorksheet = (excel.Worksheet)xlsWorkbook.Worksheets[2]; xlsWorksheet.Unprotect(); AddDemo(xlsWorksheet, rng, demography, districtLevel.LevelNumber, startDate, endDate); // Summary xlsSummary = (excel.Worksheet)xlsWorkbook.Sheets["SUMMARY"]; // ALL THE INTVS AddT3(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs); AddT2(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs); AddT1(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs); AddMDA4(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs); AddMDA3(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs); AddMDA2(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs); AddMDA1(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs); AddDistricts(xlsWorkbook, xlsWorksheet, xlsSummary, rng, reportingLevelUnits, aggIntvs); xlsWorkbook.SaveAs(filePath, excel.XlFileFormat.xlOpenXMLWorkbook, missing, missing, false, false, excel.XlSaveAsAccessMode.xlNoChange, excel.XlSaveConflictResolution.xlUserResolution, true, missing, missing, missing); xlsApp.ScreenUpdating = true; xlsApp.Visible = true; rng = null; Marshal.ReleaseComObject(xlsWorksheet); Marshal.ReleaseComObject(xlsWorkbook); Marshal.ReleaseComObject(xlsApp); System.Threading.Thread.CurrentThread.CurrentCulture = oldCI; return new ExportResult { WasSuccess = true }; } catch (Exception ex) { return new ExportResult(ex.Message); } }