public void buildFile(OperationParaVO opVO)
{
sheet.Copy(Missing.Value, workBook.Sheets[workBook.Sheets.Count]);
sheet.Name = "解析结果";
sheet.Columns.Clear();
sheet.Rows.Clear();
for (int i = 0; i < opVO.resList.Count; i++)
{
sheet.Cells[i + 1, 1] = opVO.resList[i];
}
workBook.Close();
workBooks.Close();
/**/
/*object missing=System.Reflection.Missing.Value;
* Application app = new Application();
* app.Application.Workbooks.Add(true);
* Workbook book =(Workbook)app.ActiveWorkbook;
* Worksheet sheet = (Worksheet)book.ActiveSheet;
* sheet.Cells[1, 1] = "源数据站点名称记录";
* sheet.Cells[1, 2] = "匹配总数";
* //将DataTable赋值给excel
*
* //保存excel文件
* book.SaveCopyAs("D:\\source.xls");
* //关闭文件
* book.Close(false, missing, missing);
* //退出excel
* app.Quit();*/
}
public void openExcel(OperationParaVO opVO)
{
filePatch = opVO.filePath;
workBooks = new Application().Workbooks;
workBook = workBooks.Add(filePatch);
sheet = workBook.Sheets.get_Item(opVO.sheetIndex);
}
public void analyzeExcel(OperationParaVO opVO)
{
int beginLine, endLine, groupLine;
opVO.maxLine = sheet.UsedRange.CurrentRegion.Rows.Count - 1;
beginLine = ((int)(opVO.maxLine * opVO.topline / 100)) + 2;
endLine = ((int)(opVO.maxLine - opVO.maxLine * opVO.topline / 100)) + 2;
groupLine = (endLine - beginLine) / opVO.avgline;
if (((endLine - beginLine) % opVO.avgline != 0))
{
groupLine += 1;
}
int sumVal = 0, j = 0;
String tmpCell;
for (int i = beginLine; i < endLine; i++)
{
tmpCell = ((Range)sheet.Cells[i, opVO.selectColumn]).Value;
tmpCell = tmpCell.Substring(0, tmpCell.Length - 2);
sumVal += (int.Parse(tmpCell));
j++;
if (j % groupLine == 0 && j != 0)
{
opVO.resList.Add(sumVal / groupLine);
sumVal = j = 0;
}
}
if (j % groupLine != 0 && j != 0)
{
opVO.resList.Add(sumVal / j);
}
}
private void button1_Click(object sender, EventArgs e)
{
if (topline.Text.Length == 0 ||
endline.Text.Length == 0 ||
avgline.Text.Length == 0
)
{
MessageBox.Show("不能为空");
return;
}
operationPataVO = new OperationParaVO();
try
{
operationPataVO.topline = float.Parse(topline.Text);
operationPataVO.endline = float.Parse(endline.Text);
operationPataVO.avgline = int.Parse(avgline.Text);
operationPataVO.selectColumn = int.Parse(selectColumn.Text);
operationPataVO.filePath = filepath.Text;
operationPataVO.sheetIndex = 1;
}
catch {
MessageBox.Show("参数错误");
return;
}
oe.openExcel(operationPataVO);
oe.analyzeExcel(operationPataVO);
oe.buildFile(operationPataVO);
}
