private void PerformTestCaseExcelExporter(string path)
{
// Excel.Application の新しいインスタンスを生成する
var xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbooks xlBooks;
// xlApplication から WorkBooks を取得する
// 既存の Excel ブックを開く
xlBooks = xlApp.Workbooks;
xlBooks.Open(path, ReadOnly: true);
// Excel を表示する
xlApp.Visible = true;
// マクロを実行する
// 標準モジュール内のexportメソッドに thisPath を引数で渡し実行
var filename = Path.GetFileName(path);
var thisPath = Path.GetDirectoryName(path);
xlApp.Run(filename + "!export", thisPath);
// Excel を終了する
xlApp.DisplayAlerts = false;
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
}
private static void printExcel(string excelPath)
{
Microsoft.Office.Interop.Excel.Application xApp = new Microsoft.Office.Interop.Excel.Application();
xApp.Visible = false;
Microsoft.Office.Interop.Excel.Workbook xBook = xApp.Workbooks._Open(excelPath);
Microsoft.Office.Interop.Excel.Worksheet xSheet = null;
try
{
try
{
xApp.Run("Workbook_Open");
}
catch (Exception)
{
}
xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.ActiveSheet;
xSheet.PrintOut(1, 1, 1, false);
}
catch (Exception e)
{
throw e;
}
finally
{
xBook.Close(false);
xApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xApp);
xSheet = null;
xBook = null;
xApp = null;
GC.Collect();
}
}
public override void RunCommand(object sender)
{
var engine = (Core.Automation.Engine.AutomationEngineInstance)sender;
var vInstance = v_InstanceName.ConvertToUserVariable(engine);
var excelObject = engine.GetAppInstance(vInstance);
Microsoft.Office.Interop.Excel.Application excelInstance = (Microsoft.Office.Interop.Excel.Application)excelObject;
excelInstance.Run(v_MacroName);
}
private void button1_Click(object sender, EventArgs e)
{
if (MessageBox.Show("Сформировать заявку?", "Вопрос", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
{
/////Создание объекта
Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook ObjWorkBook = ObjExcel.Workbooks.Open(Environment.CurrentDirectory + @"\template\pril_6.xlsm", 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Microsoft.Office.Interop.Excel.Worksheet ObjWorkSheet;
ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ObjWorkBook.Sheets[1];
/////////
ObjWorkSheet.Cells[6, 14] = DateTime.Today.ToShortDateString();
ObjWorkSheet.Cells[11, 2] = dataGridView1.CurrentRow.Cells[5].Value + " год";
ObjWorkSheet.Cells[15, 3] = dataGridView1.CurrentRow.Cells[4].Value;
progressBar1.Value = 0;
SqlCommand command = new SqlCommand("select * from ZAYAVKA_BODY where GUID=@gd", conn);
SqlDataAdapter da = new SqlDataAdapter(command);//Переменная объявлена как глобальная
SqlCommandBuilder cb = new SqlCommandBuilder(da);
DataSet ds = new DataSet();
command.Parameters.AddWithValue("gd", dataGridView1.CurrentRow.Cells[1].Value);
conn.Close();
//Заполнение DataGridView наименованиями полей
da.Fill(ds, "ZAYAVKA_BODY");
progressBar1.Maximum = ds.Tables[0].Rows.Count;
//Добавление кол-ва строк, столько сколько выбрано из БД запросом
for (int r = 1; r <= ds.Tables[0].Rows.Count; r++)
{
//Выполняем макрос для вставки строки
ObjExcel.Run((object)"InsRow", 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, 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, Type.Missing, Type.Missing);
}
double ssum = 0;
int t;
for (t = 24; t <= ds.Tables[0].Rows.Count + 23; t++)
{
progressBar1.Value = progressBar1.Value + 1;
ObjWorkSheet.Cells[t, 1] = ds.Tables[0].Rows[t - 24][2].ToString();
ObjWorkSheet.Cells[t, 2] = ds.Tables[0].Rows[t - 24][3].ToString();
ObjWorkSheet.Cells[t, 4] = ds.Tables[0].Rows[t - 24][5].ToString();
ObjWorkSheet.Cells[t, 5] = ds.Tables[0].Rows[t - 24][6].ToString();
ObjWorkSheet.Cells[t, 6] = dataGridView1.CurrentRow.Cells[4].Value;
ObjWorkSheet.Cells[t, 7] = ds.Tables[0].Rows[t - 24][7].ToString();
ObjWorkSheet.Cells[t, 8] = ds.Tables[0].Rows[t - 24][8].ToString();
ObjWorkSheet.Cells[t, 9] = ds.Tables[0].Rows[t - 24][9].ToString();
ObjWorkSheet.Cells[t, 10] = ds.Tables[0].Rows[t - 24][10];
ObjWorkSheet.Cells[t, 11] = ds.Tables[0].Rows[t - 24][11];
ObjWorkSheet.Cells[t, 20] = ds.Tables[0].Rows[t - 24][25].ToString();
ObjWorkSheet.Cells[t, 21] = ds.Tables[0].Rows[t - 24][12].ToString();
ObjWorkSheet.Cells[t, 22] = ds.Tables[0].Rows[t - 24][13].ToString();
ObjWorkSheet.Cells[t, 23] = ds.Tables[0].Rows[t - 24][14].ToString();
ObjWorkSheet.Cells[t, 24] = ds.Tables[0].Rows[t - 24][15].ToString();
ObjWorkSheet.Cells[t, 25] = ds.Tables[0].Rows[t - 24][16].ToString();
ObjWorkSheet.Cells[t, 26] = ds.Tables[0].Rows[t - 24][17].ToString();
ObjWorkSheet.Cells[t, 27] = ds.Tables[0].Rows[t - 24][18].ToString();
ObjWorkSheet.Cells[t, 28] = ds.Tables[0].Rows[t - 24][19].ToString();
ObjWorkSheet.Cells[t, 29] = ds.Tables[0].Rows[t - 24][20].ToString();
ObjWorkSheet.Cells[t, 30] = ds.Tables[0].Rows[t - 24][21].ToString();
ObjWorkSheet.Cells[t, 31] = ds.Tables[0].Rows[t - 24][22].ToString();
ObjWorkSheet.Cells[t, 32] = ds.Tables[0].Rows[t - 24][23].ToString();
ssum = ssum + Convert.ToDouble(ds.Tables[0].Rows[t - 24][9]);
}
ObjWorkSheet.Cells[t + 1, 8] = "Итого (общее кол-во):";
ObjWorkSheet.Cells[t + 1, 9] = ssum;
ObjExcel.Visible = true;
GC.Collect();
}
}
public void RunCommand(Command command)
{
Microsoft.Office.Interop.Excel.Application app = Instance.Default.Application;
switch (command)
{
case Command.JumpToTarget:
app.Run("RunOpenFromCell");
break;
case Command.CopyPageSetup:
app.Run("RunCopyPageSetup");
break;
case Command.SelectAllShapes:
app.Run("RunSelectAllShapes");
break;
case Command.Anova1Way:
app.Run("RunANOVA");
break;
case Command.Anova2Way:
app.Run("RunANOVA2Way");
break;
case Command.FormulaBuilder:
app.Run("RunFormulaBuilder");
break;
case Command.SelectionAssistant:
app.Run("RunSelectionAssistant");
break;
case Command.LinearRegression:
app.Run("RunLinearRegression");
break;
case Command.Correlation:
app.Run("RunCorrelation");
break;
case Command.TransposeWizard:
app.Run("RunTransposeWizard");
break;
case Command.MultiHisto:
app.Run("RunMultiHistogram");
break;
case Command.Allocate:
app.Run("RunGroupAllocation");
break;
case Command.AutomaticErrorBars:
app.Run("RunErrorBarsAuto");
break;
case Command.InteractiveErrorBars:
app.Run("RunErrorBarsInteractive");
break;
case Command.ChartDesign:
app.Run("RunChartDesign");
break;
case Command.MoveDataSeriesLeft:
app.Run("RunMoveDataSeriesLeft");
break;
case Command.MoveDataSeriesRight:
app.Run("RunMoveDataSeriesRight");
break;
case Command.Annotate:
app.Run("RunChartAnnotation");
break;
case Command.SpreadScatter:
app.Run("RunSpreadScatter");
break;
case Command.SeriesToFront:
app.Run("RunSeriesToFront");
break;
case Command.SeriesForward:
app.Run("RunSeriesForward");
break;
case Command.SeriesBackward:
app.Run("RunSeriesBackward");
break;
case Command.SeriesToBack:
app.Run("RunSeriesToBack");
break;
case Command.AddSeries:
app.Run("RunAddSeries");
break;
case Command.CopyChart:
app.Run("RunCopyChart");
break;
case Command.PointChart:
app.Run("RunPointChart");
break;
case Command.Watermark:
app.Run("RunWatermark");
break;
case Command.LegacyPrefs:
app.Run("RunPreferences");
break;
default:
throw new InvalidOperationException("Unknown legacy command " + command.ToString());
}
}
public void BackgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
Data data = (Data) e.Argument; // Cast e argument to a local Data instance
// Start Excel/Multiframe calls - Either 11 or 20 stages, depending on debugMode (11 is debugMode, skips creating frame. Does debug info only)
bool debugMode = Properties.Settings.Default.DebugMode;
int progressTotal = debugMode == true ? 11 : 20;
int progressCount = 0;
bool unableToAddQ2Loads = false;
try
{
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Opening Excel"); progressCount++;
excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = Properties.Settings.Default.DisplayExcel;
excelApp.DisplayAlerts = false;
wb = excelApp.Workbooks.Open(fileName);
ws = wb.Sheets.get_Item(1); // Expects "Input" sheet to be the first worksheet
// Insert values into Excel Inputs Sheet
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Inserting values into Input Sheet"); progressCount++;
ws.Range["windSpeedVu"].Value = data.ultimateWindSpeed;
ws.Range["windSpeedVs"].Value = data.serviceWindSpeed;
ws.Range["internalPressure"].Value = data.internalPressure;
ws.Range["shedType"].Value = data.roofType;
ws.Range["shedWallType"].Value = data.wallType;
ws.Range["shedEaveHeight"].Value = data.eaveHeight;
ws.Range["shedRoofPitch"].Value = data.roofPitch;
ws.Range["shedSpan"].Value = data.span;
ws.Range["shedBaySpacing"].Value = data.baySpacing;
ws.Range["shedNumberOfBays"].Value = data.numberOfBays;
ws.Range["shedSupports"].Value = data.supports;
ws.Range["shedEndColumnType"].Value = data.endColumnType;
ws.Range["shedEndColumnSection"].Value = data.endColumnSection;
ws.Range["shedEndRafterType"].Value = data.endRafterType;
ws.Range["shedEndRafterSection"].Value = data.endRafterSection;
ws.Range["shedMidColumnType"].Value = data.midColumnType;
ws.Range["shedMidColumnSection"].Value = data.midColumnSection;
ws.Range["shedMidRafterType"].Value = data.midRafterType;
ws.Range["shedMidRafterSection"].Value = data.midRafterSection;
ws.Range["shedMullions"].Value = data.numberOfMullions;
ws.Range["shedMullionsType"].Value = data.mullionType;
ws.Range["shedMullionsSection"].Value = data.mullionSection;
ws.Range["shedCompressionStrutType"].Value = data.strutType;
ws.Range["shedCompressionStrutSection"].Value = data.strutSection;
ws.Range["shedKneeBraceEnd"].Value = data.endKneeBraceType == "None" ? "no" : "yes";
if (data.endKneeBraceType != "None")
{
ws.Range["shedKneeBraceEndType"].Value = data.endKneeBraceType;
ws.Range["shedKneeBraceEndSection"].Value = data.endKneeBraceSection;
}
ws.Range["shedKneeBraceMid"].Value = data.midKneeBraceType == "None" ? "no" : "yes";
if (data.midKneeBraceType != "None")
{
ws.Range["shedKneeBraceMidType"].Value = data.midKneeBraceType;
ws.Range["shedKneeBraceMidSection"].Value = data.midKneeBraceSection;
}
ws.Range["shedKneeBracePercentEave"].Value = data.kneeBracePercentEave;
ws.Range["shedKneeBracePercentSpan"].Value = data.kneeBracePercentSpan;
ws.Range["shedApexBraceEnd"].Value = data.endApexBraceType == "None" ? "no" : "yes";
if (data.endApexBraceType != "None")
{
ws.Range["shedApexBraceEndType"].Value = data.endApexBraceType;
ws.Range["shedApexBraceEndSection"].Value = data.endApexBraceSection;
}
ws.Range["shedApexBraceMid"].Value = data.midApexBraceType == "None" ? "no" : "yes";
if (data.midApexBraceType != "None")
{
ws.Range["shedApexBraceMidType"].Value = data.midApexBraceType;
ws.Range["shedApexBraceMidSection"].Value = data.midApexBraceSection;
}
ws.Range["shedEavePurlinType"].Value = data.eavePurlinType;
ws.Range["shedEavePurlinSection"].Value = data.eavePurlinSection;
ws.Range["shedRoofPurlinType"].Value = data.strutType;
ws.Range["shedRoofPurlinSection"].Value = data.strutSection;
// Start calling macros from Excel Workbook
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Setting initial excel values"); progressCount++;
excelApp.Run("CsharpSetExcelInputValues");
if (debugMode == false)
{
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Setting units in Multiframe"); progressCount++;
excelApp.Run("CsharpSetUnits");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating Frame"); progressCount++;
excelApp.Run("CsharpCreateFrame");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating Knee Braces"); progressCount++;
excelApp.Run("CsharpAddKneeBraces");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating Apex Braces"); progressCount++;
excelApp.Run("CsharpAddApexBraces");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating Mullions"); progressCount++;
excelApp.Run("CsharpAddMullions");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating Eave Purlins"); progressCount++;
excelApp.Run("CsharpAddEavePurlins");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Adding Restraints and Sections"); progressCount++;
excelApp.Run("CsharpAddRestraintsAndSections");
}
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Calculating Wind Pressures"); progressCount++;
excelApp.Run("CsharpGetWindPressures");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Calculating Longwind Reductions"); progressCount++;
excelApp.Run("CsharpGetLWPressures");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Copying Pressures to Loads Sheet"); progressCount++;
excelApp.Run("CsharpCopyPressures");
if (debugMode == false)
{
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Creating New Load Cases"); progressCount++;
excelApp.Run("CsharpCreateNewLoadCases");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Setting up Loads"); progressCount++;
excelApp.Run("CsharpAddLoadsSetup");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Adding Q2 Loads"); progressCount++;
unableToAddQ2Loads = (bool)excelApp.Run("CsharpAddQ2Loads");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Adding End Portal Loads"); progressCount++;
excelApp.Run("CsharpAddEndPortalLoads");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Adding Mid Portal Loads"); progressCount++;
excelApp.Run("CsharpAddMidPortalLoads");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Adding End Wall Loads"); progressCount++;
excelApp.Run("CsharpAddEndWallLoads");
}
if (debugMode == true)
{
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Getting Geometry Ratios"); progressCount++;
data.excelGeometryRatios = (double[]) excelApp.Run("CsharpGetGeometryRatiosDebug");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Getting Pressure Coefficients"); progressCount++;
data.excelPressureCoefficients = (double[]) excelApp.Run("CsharpGetPressureCoefficientsDebug");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Getting Load Cases"); progressCount++;
data.excelLoadCases = (string[])excelApp.Run("CsharpGetLoadCasesDebug");
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Getting Loads"); progressCount++;
data.excelLoads = (string[,]) excelApp.Run("CsharpGetLoadsDebug");
}
// Alert if Q2 loads were unable to be added
if (unableToAddQ2Loads == true)
MessageBox.Show("Q2 loads were unable to be added. You will need to add them manually", "Unable to add Q2 Loads");
}
catch (Exception ex)
{
if (ex is ArgumentException || ex is ArgumentNullException)
MessageBox.Show("Error closing Excel (Marshal.ReleaseFinalComObject)\n\n" + ex.Message + "\n\n" + ex.StackTrace, "Error Closing Excel");
else
MessageBox.Show("Error closing Excel\n\n" + ex.Message + "\n\n" + ex.StackTrace, "Error Closing Excel");
}
finally
{
// Close Excel
bw.ReportProgress((int)(((double)progressCount / (double)progressTotal) * 100), "Closing Excel"); progressCount++;
GC.Collect();
GC.WaitForPendingFinalizers();
Marshal.FinalReleaseComObject(ws);
wb.Close();
Marshal.FinalReleaseComObject(wb);
excelApp.Quit();
Marshal.FinalReleaseComObject(excelApp);
}
}
public ActionResult RunXlsCampania(string cod_unidad_negocio, string fec_informe, string campania, int batch_desde, int batch_hasta)
{
if (Session["cod_unidad_negocio"] != null)
{
cod_unidad_negocio = Session["cod_unidad_negocio"].ToString();
}
var mensajeError = string.Empty;
var tablero = string.Empty;
var codUnidadNegocio = cod_unidad_negocio;
var fecInforme = fec_informe;
var batchDesde = batch_desde;
var batchHasta = batch_hasta;
var resultado = string.Empty;
var mensaje = string.Empty;
var archivoDestino = string.Empty;
int pid = 0;
//Identificar el archivo a descargar, incluyendo la ruta de acceso.
var excelFuente = string.Empty;
var excelDestino = string.Empty;
var fileDestino = string.Empty;
excelFuente = Server.MapPath("~/xlso/KPI_PI_Campañas.xlsm");
DateTime?dtfecInforme = null;
if (!(fec_informe == "" || fec_informe == string.Empty))
{
dtfecInforme = DateTime.ParseExact(fec_informe, "dd/MM/yyyy", CultureInfo.InvariantCulture);
//Cambiamos el nombre del archivo Destino
fileDestino = "Campaña" + "_" + campania + "_" + codUnidadNegocio + "_" + dtfecInforme.Value.ToString("yyMMdd") + DateTime.Now.ToString("hhmmsss") + ".xlsx";
}
else
{
fileDestino = "Campaña" + "_" + campania + "_" + codUnidadNegocio + "_" + batchDesde + "_" + batch_hasta + "_" + DateTime.Now.ToString("hhmmsss") + ".xlsx";
}
excelDestino = Server.MapPath("~/xlsd/" + fileDestino);
try
{
var excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Application.Visible = false;
excelApp.DisplayAlerts = false;
var excelWorkbook = excelApp.Workbooks.Open(Filename: excelFuente, ReadOnly: true);
GetWindowThreadProcessId(excelApp.Hwnd, out pid);
excelWorkbook.Sheets["Registro"].Range("A2").Value = codUnidadNegocio;
excelWorkbook.Sheets["Registro"].Range("B2").Value = campania;
var fecExcel = DateTime.ParseExact(fec_informe, "dd/MM/yyyy", null).ToString("MM/dd/yyyy");
excelWorkbook.Sheets["Registro"].Range("C2").Value = fecExcel;
excelWorkbook.Sheets["Registro"].Range("D2").Value = excelDestino;
excelWorkbook.Sheets["Registro"].Range("E2").Value = batchDesde;
excelWorkbook.Sheets["Registro"].Range("F2").Value = batchHasta;
try
{
mensajeError = excelApp.Run("BtnRegistro_Carga");
}
catch (Exception)
{
mensajeError = "ERROR: En la ejecución de Excel";
}
if (mensajeError == null)
{
mensajeError = "";
}
mensajeError.Replace("'", "´");
mensajeError.Replace(((char)34).ToString(), "´");
mensajeError.Replace(((char)13).ToString(), "\r");
mensajeError.Replace(((char)10).ToString(), "\r");
//Close Excel
excelWorkbook.Close(SaveChanges: false);
excelWorkbook = null;
excelApp.Quit();
archivoDestino = excelDestino.Replace(@"\", "/");
try
{
//procesoExcel.Kill();
Process.GetProcessById(pid).Kill();
}
catch (Exception)
{
}
}
catch (Exception ex)
{
System.Threading.Thread.Sleep(1000); //Espera un segundo para intentarlo de nuevo
mensajeError += " Servicio de Excel en estos momentos se encuentra sin recursos.\r Por favor intentelo mas tarde.";
try
{
//procesoExcel.Kill();
Process.GetProcessById(pid).Kill();
}
catch (Exception)
{
}
}
if (mensajeError == "")
{
return(Json(
new Response
{
Status = HttpStatusCode.OK,
Message = archivoDestino
},
JsonRequestBehavior.AllowGet));
}
return(Json(
new Response
{
Status = HttpStatusCode.BadRequest,
Message = mensajeError
},
JsonRequestBehavior.AllowGet));
}
