C#读写Excel
读 Range ce=sheet.Cells[2,3]; string vv=ce.value; 写 //注意： // * Excel中形如Cells[x][y]的写法，前面的数字是RowIndex，后面的数字是ColumnIndex! // * Excel中的行、列都是从1开始的，而不是0 //1.制作一个新的Excel文档实例 Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();//创建excel Workbook myWorkBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);//创建工作簿（workBook；即excel文件主体本身） //Worksheet sheet = (Worksheet)workBook.Worksheets[1];//创建主工作簿 //增加查询信息Sheet // Microsoft.Office.Interop.Excel.Worksheet sarchInfoSheet = myWorkBook.Sheets.Add(); sarchInfoSheet.Name = "查询条件";//确定sheet名称 sarchInfoSheet.Cells[1, 1] = "起始时间：";//起始时间 sarchInfoSheet.Cells[1, 2] = SearchPageModel.mySearchPageModel.startDateTime.ToString();//起始时间 sarchInfoSheet.Cells[2, 1] = "结束时间：";//结束时间 sarchInfoSheet.Cells[2, 2] = SearchPageModel.mySearchPageModel.endDateTime.ToString();//结束时间 sarchInfoSheet.Cells[3, 1] = "手机型号："; sarchInfoSheet.Cells[3, 2] = SearchPageModel.mySearchPageModel.selectedModel; sarchInfoSheet.Cells[4, 1] = "测试计划："; sarchInfoSheet.Cells[4, 2] = SearchPageModel.mySearchPageModel.selectedFile; sarchInfoSheet.Cells[5, 1] = "失败项目："; sarchInfoSheet.Cells[5, 2] = SearchPageModel.mySearchPageModel.selectedItem; sarchInfoSheet.Cells[6, 1] = "失败原因："; sarchInfoSheet.Cells[6, 2] = SearchPageModel.mySearchPageModel.selectedReason; sarchInfoSheet.Cells[7, 1] = "PhoneID："; sarchInfoSheet.Cells[7, 2] = SearchPageModel.mySearchPageModel.phoneID; sarchInfoSheet.Cells[8, 1] = "工位IP："; sarchInfoSheet.Cells[8, 2] = SearchPageModel.mySearchPageModel.computerIP; sarchInfoSheet.Cells[9, 1] = "计算机名："; sarchInfoSheet.Cells[9, 2] = SearchPageModel.mySearchPageModel.computerName; sarchInfoSheet.Cells[10, 1] = "线程号码："; sarchInfoSheet.Cells[10, 2] = SearchPageModel.mySearchPageModel.threadNumber; sarchInfoSheet.Cells[11, 1] = "操作人员："; sarchInfoSheet.Cells[11, 2] = SearchPageModel.mySearchPageModel.tsOperator; sarchInfoSheet.Cells[12, 1] = "测试类型："; sarchInfoSheet.Cells[12, 2] = SearchPageModel.mySearchPageModel.testType; sarchInfoSheet.Cells[13, 1] = "软件版本："; sarchInfoSheet.Cells[13, 2] = SearchPageModel.mySearchPageModel.softVersion; sarchInfoSheet.Cells[14, 1] = "测试OK："; sarchInfoSheet.Cells[14, 2] = SearchPageModel.mySearchPageModel.testResultOK; sarchInfoSheet.Cells[15, 1] = "测试NG："; sarchInfoSheet.Cells[15, 2] = SearchPageModel.mySearchPageModel.testResultNG; sarchInfoSheet.Cells[16, 1] = "Data is exported from HustAnalyser."; // Worksheet myWorkSheet = myWorkBook.Sheets.Add(); //2.设置Excel分页卡标题 myWorkSheet.Name = "详细数据";//确定sheet名称 //方法二：生成Excel中列头名称 for (int i = 0; i < dataSouce.Columns.Count; i++) { myWorkSheet.Cells[1, i + 1] = dataSouce.Columns[i].ColumnName;//输出DataGridView列头名 if (i>= addCollumNum && searchResults[i - addCollumNum].result == "FAIL") { Microsoft.Office.Interop.Excel.Range columnRange = myWorkSheet.Columns.EntireColumn[i + 1]; columnRange.Font.Color = 0x0000FF;//(蓝00绿00红00) } } //RunInfo = "testDataArray复制数据到Excel"; //方法二：整体赋值 Microsoft.Office.Interop.Excel.Range dataSourceRange = myWorkSheet.Range[myWorkSheet.Cells[2, 1], myWorkSheet.Cells[dataSouce.Rows.Count + 1, dataSouce.Columns.Count]]; dataSourceRange.Value = testDataArray; //给Exccel中的Range整体赋值 //RunInfo = "Excel格式设置..."; dataSourceRange.EntireColumn.AutoFit(); //设定Excel列宽度自适应 //5.设置格式 Microsoft.Office.Interop.Excel.Range rowRange = myWorkSheet.Rows.EntireRow[1];//首行 rowRange.Font.ColorIndex = ColorIndex.深蓝;//首行颜色 rowRange.Interior.Color = 0xA6AA00;//(蓝A6绿AA红00) 首行背景 rowRange.Font.Bold = true;//首行加粗 Microsoft.Office.Interop.Excel.Range colRange = myWorkSheet.Columns.EntireColumn[1];//首列 //colRange.Font.ColorIndex = ColorIndex.深蓝;//首列颜色 colRange.Font.Bold = true;//首列加粗 //冻结首行首列 excelApp.ActiveWindow.SplitRow = 1;//设置将指定窗口拆分成窗格处的行号（拆分线以上的行数） excelApp.ActiveWindow.SplitColumn = 1;//设置将指定窗口拆分成窗格处的列号（拆分线左侧的列数） excelApp.ActiveWindow.FreezePanes = true;//冻结首行首列 //边框实线 Microsoft.Office.Interop.Excel.Range dataRange = myWorkSheet.Range[myWorkSheet.Cells[1, 1], myWorkSheet.Cells[dataSouce.Rows.Count, dataSouce.Columns.Count]];// dataRange.Borders.LineStyle = 1;//设置边框为实线 //myWorkSheet.Cells.Borders.LineStyle = 1;//设置整个数据区边框为实线 //auto adjust column width (according to content)调整列宽 //Microsoft.Office.Interop.Excel.Range allColumn = myWorkSheet.Columns; //allColumn.AutoFit();//调整列宽 dispatcher.Invoke(new System.Action(() => { //以下为Excel保存过程----------------------------- string excelFile = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\HustAnalyser导出info" + DateTime.Now.ToString("-yyyyMMddHHmmss") + ".xlsx"; //默认桌面 System.Windows.Forms.SaveFileDialog saveFileDialog = new System.Windows.Forms.SaveFileDialog(); saveFileDialog.Filter = "Excel文件(*.xlsx)|*.xlsx"; saveFileDialog.FileName = "HustAnalyser导出info" + DateTime.Now.ToString("-yyyyMMddHHmmss"); if (saveFileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) { excelFile = saveFileDialog.FileName; } //myWorkBook.Save();//保存结果 myWorkBook.SaveAs(excelFile);//保存结果 excelApp.Quit(); excelApp = null; System.Diagnostics.Process.Start("Explorer", "/select," + excelFile); //Excel保存结束------------------------------------ }));
