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