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