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儲存結束------------------------------------
                }));