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