public void parseColumn(Models.Instance modelInstance, Models.RowData modelRowData) { column1 = new ArrayList(); column2 = new ArrayList(); column3 = new ArrayList(); for (int i = 0; i < modelInstance.data.Count; i++) { if (modelInstance.data[i] != null) { if (modelInstance.data[i].ToString().Trim() != "") { try { Double data = Double.Parse(modelInstance.data[i].ToString().Trim()); column1.Add(modelRowData.node[i]); column2.Add(modelRowData.dataTime[i]); column3.Add(data); } catch { } } } } }
public void insertPicture(string sheetName, Models.RowData modelRowData, System.Windows.Forms.TextBox textBox) { //删除空白的实例 for (int i = modelRowData.instances.Count - 1; i >= 0; i--) { bool isDelete = true; Models.Instance model_Instance = (Models.Instance)modelRowData.instances[i]; for (int j = 0; j < model_Instance.pictures.Count; j++) { if (model_Instance.pictures[j] != null) { isDelete = false; } } if (isDelete) { modelRowData.instances.RemoveAt(i); } } //delete sheet if exist ISheet sheet = null; sheet = workbook.GetSheet(sheetName); if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet { sheet = workbook.CreateSheet(sheetName); } HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); for (int i = 0; i < modelRowData.instances.Count; i++) { Models.Instance model_Instance = (Models.Instance)modelRowData.instances[i]; for (int j = 0; j < model_Instance.pictures.Count; j++) { if (model_Instance.pictures[j] != null) { string picturePath = model_Instance.pictures[j].ToString(); //读取图片 byte[] bytes = System.IO.File.ReadAllBytes(picturePath); int pictureIdx = workbook.AddPicture(bytes, PictureType.PNG); //add a picture HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 255, 255, 4 + (j * 12), 2 + (i * 32), 14 + (j * 12), 10 + (i * 32)); HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); pict.Resize(); textBox.AppendText(" " + picturePath.Substring(picturePath.LastIndexOf("\\") + 1) + "\r\n"); } } } fs = new FileStream(fileName, FileMode.Open, FileAccess.Write); workbook.Write(fs); fs.Close(); textBox.AppendText("Insert pictures succeed\r\n"); }
public void GeneratePictures(Models.RowData modelRowData, System.Windows.Forms.TextBox textBox) { try { foreach (Process proc in Process.GetProcessesByName("Mtb")) { proc.Kill(); } } catch (Exception ex) { } Mtb.Application MtbApp = new Mtb.Application(); MtbApp.UserInterface.Visible = true; //创建图片文件夹 textBox.AppendText("Creating picture folder:" + modelRowData.filePath + "\r\n"); if (!Directory.Exists(modelRowData.filePath)) //判断文件夹是否已经存在 { Directory.CreateDirectory(modelRowData.filePath); //创建文件夹 } textBox.AppendText("Generating pictures...\r\n"); foreach (Models.Instance modelInstance in modelRowData.instances) { try { textBox.AppendText(" " + modelInstance.title + " "); GeneratePicturesInstance(MtbApp, modelInstance, modelRowData); textBox.AppendText("Succeed\r\n"); } catch { Mtb.Project MtbProj = MtbApp.ActiveProject; MtbProj.Delete(); MtbApp.New(); textBox.AppendText("Failed\r\n"); } } textBox.AppendText("Generate pictures succeed\r\n"); MtbApp.Quit(); }
private void button2_Click(object sender, EventArgs e) { this.textBoxOutPut.Text = "Reading Excel File..."; ExcelHelper excelHelper = new ExcelHelper(this.textBoxFile.Text.Trim()); Models.RowData rowData = excelHelper.getRowData("Raw data-Flex"); if (rowData == null) { this.textBoxOutPut.AppendText("Failed\r\n"); } else { this.textBoxOutPut.AppendText("Succeed\r\n"); MinitabHelper minitab = new MinitabHelper(); minitab.GeneratePictures(rowData, this.textBoxOutPut); this.textBoxOutPut.AppendText("Inserting picture to Excel...\r\n"); excelHelper.insertPicture("Graphs Minitab", rowData, this.textBoxOutPut); MessageBox.Show("Operation succeed, Please open excel file and check \"Graphs Minitab\" worksheet."); } }
public void GeneratePicturesInstance(Mtb.Application MtbApp, Models.Instance modelInstance, Models.RowData modelRowData) { Mtb.Project MtbProj = MtbApp.ActiveProject; parseColumn(modelInstance, modelRowData); //计算标准差 CalculateSTDVE(column3, modelInstance); Mtb.Columns MtbColumns = MtbProj.ActiveWorksheet.Columns; Mtb.Column MtbColumn1 = MtbColumns.Add(null, null, 1); MtbColumn1.SetData(column1.ToArray()); Mtb.Column MtbColumn2 = MtbColumns.Add(null, null, 1); MtbColumn2.SetData(column2.ToArray()); Mtb.Column MtbColumn3 = MtbColumns.Add(null, null, 1); MtbColumn3.SetData(column3.ToArray()); try { string imgPath = Path.Combine(modelRowData.filePath, parseFileName(modelInstance.title) + " Process Capability"); string lspec = Double.IsNaN(modelInstance.LCL) ? "" : "Lspec " + modelInstance.LCL.ToString("f3") + ";"; string uspec = Double.IsNaN(modelInstance.UCL) ? "" : "Uspec " + modelInstance.UCL.ToString("f3") + ";"; MtbProj.ExecuteCommand(" Capa C3 " + column1.Count + "; " + lspec + uspec + " Pooled; AMR; UnBiased; OBiased; Toler 6; Within; Percent; Title \"" + getPictureTitle(0, modelInstance) + "\"; CStat."); Mtb.Graph MtbGraph = MtbProj.Commands.Item(MtbProj.Commands.Count).Outputs.Item(1).Graph; MtbGraph.SaveAs(imgPath, true, Mtb.MtbGraphFileTypes.GFPNGHighColor, 768, 531); modelInstance.pictures.Add(imgPath + ".png"); } catch { modelInstance.pictures.Add(null); } try { string imgPath = Path.Combine(modelRowData.filePath, parseFileName(modelInstance.title) + " Individual Polt"); MtbProj.ExecuteCommand(" Indplot ( C3 ) * C1; Title \"" + getPictureTitle(1, modelInstance) + "\"; Individual."); Mtb.Graph MtbGraph2 = MtbProj.Commands.Item(MtbProj.Commands.Count).Outputs.Item(1).Graph; MtbGraph2.SaveAs(imgPath, true, Mtb.MtbGraphFileTypes.GFPNGHighColor, 768, 531); modelInstance.pictures.Add(imgPath + ".png"); } catch { modelInstance.pictures.Add(null); } try { string imgPath = Path.Combine(modelRowData.filePath, parseFileName(modelInstance.title) + " Scatter Plot"); MtbProj.ExecuteCommand(" Plot C3*C2; Symbol C1; Title \"" + getPictureTitle(2, modelInstance) + "\"; JITTER."); Mtb.Graph MtbGraph3 = MtbProj.Commands.Item(MtbProj.Commands.Count).Outputs.Item(1).Graph; MtbGraph3.SaveAs(imgPath, true, Mtb.MtbGraphFileTypes.GFPNGHighColor, 768, 531); modelInstance.pictures.Add(imgPath + ".png"); } catch { modelInstance.pictures.Add(null); } try { string imgPath = Path.Combine(modelRowData.filePath, parseFileName(modelInstance.title) + " Probability Plot"); MtbProj.ExecuteCommand(" PPlot C3; Normal; Symbol; FitD; NoCI; Grid 2; Grid 1; MGrid 1; Title \"" + getPictureTitle(3, modelInstance) + "\"."); Mtb.Graph MtbGraph4 = MtbProj.Commands.Item(MtbProj.Commands.Count).Outputs.Item(1).Graph; MtbGraph4.SaveAs(imgPath, true, Mtb.MtbGraphFileTypes.GFPNGHighColor, 768, 531); modelInstance.pictures.Add(imgPath + ".png"); } catch { modelInstance.pictures.Add(null); } MtbProj.Delete(); MtbApp.New(); }
public Models.RowData getRowData(string sheetName) { ISheet sheet = null; Models.RowData modelRowData = new Models.RowData(); try { modelRowData.filePath = fileName.Substring(0, fileName.LastIndexOf(".")) + " images"; fs = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite); if (fileName.IndexOf(".xlsx") > 0) // 2007版本 { workbook = new XSSFWorkbook(fs); } else if (fileName.IndexOf(".xls") > 0) // 2003版本 { workbook = new HSSFWorkbook(fs); } if (sheetName != null) { sheet = workbook.GetSheet(sheetName); if (sheet == null) //如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet { sheet = workbook.GetSheetAt(0); } } else { sheet = workbook.GetSheetAt(0); } if (sheet != null) { int startRow = 5; int startColumn = 7; //获取实例的个数 IRow titleRow = sheet.GetRow(1); int columnCount = titleRow.LastCellNum; //测试专用,用于减小列数 //columnCount = columnCount > 14 ? 14 : columnCount; //获取数据行数 int rowCount = sheet.LastRowNum; for (int i = startColumn; i < columnCount; i++) { modelRowData.instances.Add(new Models.Instance()); //添加title if (titleRow.GetCell(i) != null) { ((Models.Instance)modelRowData.instances[i - startColumn]).title = titleRow.GetCell(i).ToString(); } } //更新实例信息 //添加限制类型 IRow limTypeRow = sheet.GetRow(2); for (int i = startColumn; i < columnCount; i++) { if (limTypeRow.GetCell(i) != null) { ((Models.Instance)modelRowData.instances[i - startColumn]).limType = limTypeRow.GetCell(i).ToString(); } } //添加下限 IRow lowerLimitRow = sheet.GetRow(3); for (int i = startColumn; i < columnCount; i++) { if (lowerLimitRow.GetCell(i) != null) { try { ((Models.Instance)modelRowData.instances[i - startColumn]).lowerLimit = Double.Parse(lowerLimitRow.GetCell(i).ToString()); } catch (Exception exx) { } } } //添加上限 IRow upLimitRow = sheet.GetRow(4); for (int i = startColumn; i < columnCount; i++) { if (upLimitRow.GetCell(i) != null) { try { ((Models.Instance)modelRowData.instances[i - startColumn]).upLimit = Double.Parse(upLimitRow.GetCell(i).ToString()); } catch (Exception exx) { } } } //添加名称 IRow nameRow = sheet.GetRow(5); for (int i = startColumn; i < columnCount; i++) { if (nameRow.GetCell(i) != null) { ((Models.Instance)modelRowData.instances[i - startColumn]).name = nameRow.GetCell(i).ToString(); } } //添加单位 IRow unitRow = sheet.GetRow(5); for (int i = startColumn; i < columnCount; i++) { if (unitRow.GetCell(i) != null) { ((Models.Instance)modelRowData.instances[i - startColumn]).unit = unitRow.GetCell(i).ToString(); } } //更新实例数据信息 for (int i = startRow + 1; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) { continue; //没有数据的行默认是null } //Node if (row.GetCell(4) != null) { try { modelRowData.node.Add(float.Parse(row.GetCell(4).ToString())); } catch { modelRowData.node.Add(null); } } else { modelRowData.node.Add(null); } //Datetime if (row.GetCell(3) != null) { try { modelRowData.dataTime.Add(row.GetCell(3).DateCellValue); } catch { modelRowData.dataTime.Add(null); } } else { modelRowData.dataTime.Add(null); } //instances for (int j = startColumn; j < columnCount; j++) { if (row.GetCell(j) != null) { try { ((Models.Instance)modelRowData.instances[j - startColumn]).data.Add(row.GetCell(j).ToString()); } catch { ((Models.Instance)modelRowData.instances[j - startColumn]).data.Add(null); } } else { ((Models.Instance)modelRowData.instances[j - startColumn]).data.Add(null); } } } } fs.Close(); return(modelRowData); } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); fs.Close(); return(null); } }