读
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保存结束------------------------------------
}));
