在软件开发中难免碰到小型数据统计等功能需求,使用Excel也就成了常使用的方法之一。总结一下之前项目中所用到的Excel部分代码,以备后续查阅。 首先得在“Refrence”中添加Excel的组件。using System; using System.Collections.Generic; using System.Text; //引用Excel库 using Excel = Microsoft.Office.Interop.Excel; using System.Collections; using System.Reflection; using System.Windows.Forms; using System.IO; namespace ExcelTest { class ExcelClass { #region private ArrayList GetArrayListOfData = new ArrayList(); private ArrayList GetArrayListOfAverage = new ArrayList();*/ public ArrayList readTestData = new ArrayList(); public ArrayList readTestAverage = new ArrayList(); #endregion public void WriteSumExcel(string path) { string[,] sumdata = new string[RowsCount, 7]; sumdata[0, 0] = "编号"; sumdata[0, 1] = "用例名称"; sumdata[0, 2] = "用时"; sumdata[0, 3] = "平均"; sumdata[0, 4] = "最大"; sumdata[0, 5] = "最小"; sumdata[0, 6] = "结果"; for (int i = 0; i < RowsCount - 1; i++) { sumdata[1 + i, 0] = (1 + i).ToString(); sumdata[1 + i, 1] = ArraySumData[i, 0]; sumdata[1 + i, 2] = ArraySumData[i, 1]; sumdata[1 + i, 3] = ArraySumData[i, 2]; sumdata[1 + i, 4] = ArraySumData[i, 3]; sumdata[1 + i, 5] = ArraySumData[i, 4]; sumdata[1 + i, 6] = ArraySumData[i, 5]; } //将ArraylistOfTestInfo的信息录入Excel表格中,并设置Excel表格的格式 Excel.Application SuitExcel = new Excel.Application(); SuitExcel.Visible = false; Excel.Workbook SuitBook = SuitExcel.Workbooks.Add(Value.Missing); Excel.Worksheet SuitSheet = (Excel.Worksheet)SuitBook.Worksheets[1]; Excel.Range Title = SuitSheet.get_Range("A1", "G1");//选定范围 Title.Merge(0);//合并 string ExcelTitle = "TryTry"; SuitSheet.Cells[1, 1] = ExcelTitle;//放置标题 SuitSheet.get_Range("A1", "G1").HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//单元格的位置调整 SuitSheet.get_Range("A1", "A1").Font.Bold = true;//黑体 SuitSheet.get_Range("A1", "A1").Font.ColorIndex = 5;//颜色 SuitSheet.get_Range("A1", "A1").Font.Size = 20;//大小 Excel.Range A1 = SuitSheet.get_Range("A1", "A1"); A1.ColumnWidth = 7;//列宽 SuitSheet.get_Range(SuitSheet.Cells[2, 1], SuitSheet.Cells[2 + RowsCount - 1, 7]).Value2 = sumdata;//给指定位置存储数据 int iRowCount = SuitSheet.UsedRange.Cells.Rows.Count;//获取已使用的行数 Excel.Range rBorders = SuitSheet.get_Range(SuitSheet.Cells[2, 1], SuitSheet.Cells[iRowCount, 7]); rBorders.Borders.LineStyle = 1; rBorders.Font.Size = 10; try//下面的几条语句是Excel表保存的流程,在保存时一定要注意 { SuitExcel.ActiveWorkbook.SaveAs(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);//Workbook的保存 SuitBook.Save();//保存 SuitBook = null; SuitSheet = null; SuitExcel.Quit(); SuitExcel = null; } catch { MessageBox.Show("数据保存未完成!"); SuitBook = null; SuitSheet = null; SuitExcel.Quit(); SuitExcel = null; } } private void GetDataFromExcel(string path)//path 为Excel文件的路径 { int intDataPosition = 0; readTestAverage.Clear(); readTestData.Clear(); Excel.Application excelTestTable = new Excel.Application(); Excel.Workbook xBook = excelTestTable.Workbooks._Open(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);//Open Excel File Excel.Worksheet xSheet = (Excel.Worksheet)xBook.Sheets[1]; int iRowsCount = xSheet.UsedRange.Cells.Rows.Count; //获得Excel使用的最大行数 Excel.Range rangeTestlimit = xSheet.get_Range(xSheet.Cells[1, 1], xSheet.Cells[iRowsCount, 3]); System.Array find = (System.Array)rangeTestlimit.Formula; try { int i = 1; //查找数据的起始位置 while (i < iRowsCount) { if (find.GetValue(i, 1).ToString().Trim() == "Start Flag") { intDataPosition = 0; intDataPosition = i + 1; break; } i++; } if (intDataPosition == 0) { xSheet = null; xBook = null; excelTestTable.Quit(); excelTestTable = null; return; } else { Excel.Range range = xSheet.get_Range(xSheet.Cells[intDataPosition, 2], xSheet.Cells[iRowsCount, 2]); //获取指定位置 Excel.Range rangeAve = xSheet.get_Range(xSheet.Cells[intDataPosition, 3], xSheet.Cells[iRowsCount, 3]); //获取位置 System.Array values = (System.Array)range.Formula;//指定位置数据的格式转换 System.Array average = (System.Array)rangeAve.Formula; if (values != null) { for (int k = 1; k < iRowsCount - intDataPosition; k++) {//将数据转换格式后保存至Arraylist中 if (values.GetValue(k, 1).ToString() != "") { readTestData.Add(values.GetValue(k, 1).ToString()); readTestAverage.Add(average.GetValue(k, 1).ToString()); } } } } } catch { MessageBox.Show("查找参数!"); xSheet = null; xBook = null; excelTestTable.Quit(); excelTestTable = null; return; } xSheet = null; xBook = null; excelTestTable.Quit(); excelTestTable = null; } } }