C#操作Excel的多種方式小結(jié)

更新時(shí)間：2024年11月12日 08:38:05 作者：無熵~

在項(xiàng)目開發(fā)過程和辦公過程中,經(jīng)常要遇到對Excel進(jìn)行創(chuàng)建,讀寫等操作,本文為大家整理了一些C#操作Excel的方法,感興趣的小伙伴可以了解一下

Excel操作幾種方式

使用OleDb(過時(shí))

使用Microsoft.Office.Interop.Excel COM組件(兼容性問題)

使用開源庫NPOI(常用，操作豐富)

使用OpenXml(效率高)

使用OleDb

通過OleDb操作excel需要安裝AccessDatabaseEngine組件，對Excel文件的格式有一定的限制，不支持一些特定的Excel功能。性能可能不如其他方法,比較老舊，基本不用。

       string excelFilePath = "D:\\Test\\test.xlsx";
       private void OpenExcelByOleDb()
       {
           string connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={excelFilePath};Extended Properties='Excel 12.0;HDR=YES;'";
           using (OleDbConnection connection = new OleDbConnection(connectionString))
           {
               try
               {
                   connection.Open();
                   Console.WriteLine("鏈接成功!");
 
                   // 獲取Excel文件中第一個(gè)工作表的數(shù)據(jù)
                   string query = "SELECT * FROM [Sheet1$]";
                   using (OleDbCommand command = new OleDbCommand(query, connection))
                   using (OleDbDataReader reader = command.ExecuteReader())
                   {
                       while (reader.Read())
                       {
                           // 假設(shè)第一列是字符串，第二列是整數(shù)
                           string stringValue = reader.GetString(0);
                           int intValue = reader.GetInt32(1);
 
                           Console.WriteLine($"String value: {stringValue}, Integer value: {intValue}");
                       }
                   }
               }
               catch (Exception ex)
               {
                   Console.WriteLine($"錯(cuò)誤: {ex.Message}");
               }
           }
       }

Microsoft.Office.Interop.Excel

通過Microsoft.Office.Interop.Excel COM組件操作Excel可以實(shí)現(xiàn)對Excel文件的高度控制，支持復(fù)雜的Excel操作。但對資源的管理需要謹(jǐn)慎處理，易造成資源泄漏且兼容性不高。

        string excelFilePath = "D:\\Test\\test.xlsx";
        private void OpenExcelByInteropExcel()
        {
            // 創(chuàng)建Excel應(yīng)用程序?qū)ο?
            Excel.Application excelApp = new Excel.Application();
            excelApp.Visible = true; // 可見Excel應(yīng)用程序界面
 
            // 打開Excel文件
            Excel.Workbook workbook = excelApp.Workbooks.Open(excelFilePath);
            Excel.Worksheet worksheet = workbook.Sheets[1] as Excel.Worksheet;
 
            // 讀取或?qū)懭霐?shù)據(jù)
            Excel.Range range = worksheet.UsedRange;
            for (int row = 1; row <= range.Rows.Count; row++)
            {
                for (int column = 1; column <= range.Columns.Count; column++)
                {
                    // 處理單元格數(shù)據(jù)
                    var cellValue = range.Cells[row, column].Value;
                    Console.WriteLine(cellValue.ToString());
                    //range.Cells[row, column].Value = 1;
                }
            }
            #region 保存模板
            //object Nothing = System.Reflection.Missing.Value;
            //Microsoft.Office.Interop.Excel.Workbook workbook1 = excelApp.Workbooks.Add(Nothing);
            //Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet2"];//打開Sheet2
            //worksheet1.Copy(workbook1.Sheets["Sheet1"], Type.Missing);//復(fù)制模板Sheet1內(nèi)容
            //workbook.Close(false, Type.Missing, Type.Missing);
            //workbook1.SaveAs("modelpath", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            //workbook1.Close(false, Type.Missing, Type.Missing);
            #endregion
 
            // 釋放資源
            workbook.Close(false);
            excelApp.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
        }

使用開源庫NPOI

使用開源庫NPOI操作Excel跨平臺(tái)，不依賴于Microsoft Office，適用于在服務(wù)器端等環(huán)境中操作Excel文件。支持讀取和寫入Excel文件，并提供了豐富的API。但對于復(fù)雜的Excel操作，不如COM組件靈活，無法實(shí)現(xiàn)一些高級(jí)功能。Nuget引用NPOI組件。

  string excelFilePath = "D:\\Test\\test.xlsx";
  private void OpenExcelByNPOI()
  {
      // 讀取Excel文件
      using (FileStream fs = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
      {
          IWorkbook workbook = new XSSFWorkbook(fs);
          ISheet sheet = workbook.GetSheetAt(0);
 
          // 遍歷每一行
          for (int row = 0; row <= sheet.LastRowNum; row++)
          {
              IRow currentRow = sheet.GetRow(row);
              if (currentRow != null) // 確保行不為空
              {
                  // 遍歷每一列
                  for (int column = 0; column < currentRow.LastCellNum; column++)
                  {
                      ICell currentCell = currentRow.GetCell(column);
                      if (currentCell != null) // 確保單元格不為空
                      {
                          // 處理單元格數(shù)據(jù)
                          var cellValue = currentCell.ToString();
                          Console.WriteLine(cellValue);
                      }
                  }
              }
          }
      }
  }

使用OpenXml

使用OpenXml操作Excel，它直接對文件流進(jìn)行操作，而無需將整個(gè)文檔加載到內(nèi)存中，無需安裝 Microsoft Office，非常適合服務(wù)器端應(yīng)用程序和批處理。Nuget引用DocumentFormat.OpenXml 組件。

        string excelFilePath = "D:\\Test\\test.xlsx";
        private void OpenExcelByOpenXml()
        {
            //流式傳輸文件，性能高
            using (SpreadsheetDocument doc = SpreadsheetDocument.Open(excelFilePath, false))
            {
                WorkbookPart workbookPart = doc.WorkbookPart;
                Sheet sheet = workbookPart.Workbook.Sheets.GetFirstChild<Sheet>();
                WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
 
                OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
                while (reader.Read())
                {
                    if (reader.ElementType == typeof(DocumentFormat.OpenXml.Spreadsheet.Row))
                    {
                        DocumentFormat.OpenXml.Spreadsheet.Row row = (DocumentFormat.OpenXml.Spreadsheet.Row)reader.LoadCurrentElement();
                        foreach (DocumentFormat.OpenXml.Spreadsheet.Cell cell in row.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>())
                        {
                            string cellValue = GetCellValue(doc, cell);
                            Console.Write(cellValue + " ");
                        }
                        Console.WriteLine();
                    }
                }
            }
 
        }
        /// <summary>
        /// OpenXml獲取單元格值
        /// 流式傳輸
        /// </summary>
        /// <param name="doc"></param>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static string GetCellValue(SpreadsheetDocument doc, DocumentFormat.OpenXml.Spreadsheet.Cell cell)
        {
            SharedStringTablePart stringTablePart = doc.WorkbookPart.SharedStringTablePart;
            string value = cell.CellValue.InnerXml;
 
            if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
            {
                return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
            }
            else
            {
                return value;
            }
        }

Excel與DatagridView之間操作

Excel文件導(dǎo)入DateGridView

  /// <summary>
  /// Excel導(dǎo)入DataGridView
  /// </summary>
  /// <param name="excelFilePath"></param>
  /// <param name="dataGridView"></param>
  public static void ImportExcelToDataGridView(string excelFilePath, DataGridView dataGridView)
  {
      Excel.Application excelApp = new Excel.Application();
      Excel.Workbook workbook = excelApp.Workbooks.Open(excelFilePath);
      Excel.Worksheet worksheet = workbook.Sheets[1];
      Range usedRange = worksheet.UsedRange;
 
      for (int i = 1; i <= usedRange.Columns.Count; i++)
      {
          DataGridViewColumn column = new DataGridViewColumn();
          column.Name = "Column" + i;
          column.HeaderText = "Column" + i;
          column.CellTemplate = new DataGridViewTextBoxCell();
          dataGridView.Columns.Add(column);
      }
      dataGridView.Rows.Clear();
      for (int i = 1; i <= usedRange.Rows.Count; i++)
      {
          DataGridViewRow row = new DataGridViewRow();
         
          for (int j = 1; j <= usedRange.Columns.Count; j++)
          {
              row.Cells.Add(new DataGridViewTextBoxCell());
              row.Cells[j - 1].Value = usedRange.Cells[i, j].Value;
 
          }
          dataGridView.Rows.Add(row);
      }
 
      workbook.Close();
      excelApp.Quit();
 
      System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
      System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
      System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
 
      worksheet = null;
      workbook = null;
      excelApp = null;
 
      GC.Collect();
  }

DateGridView導(dǎo)出Excel文件

        /// <summary>
        /// Datagridview導(dǎo)出excel
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="myDGV"></param>
        private void DatagridViewToExcel( DataGridView myDGV)
        {
            string saveFileName = "";
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xls";
            saveDialog.Filter = "Excel文件|*.xls";
            if(saveDialog.ShowDialog()==DialogResult.Cancel)
            {
                return;
            }
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0) return; //被點(diǎn)了取消
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("無法創(chuàng)建Excel對象，可能您的機(jī)子未安裝Excel");
                return;
            }
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
                                                                                                                                  //寫入標(biāo)題
            for (int i = 0; i < myDGV.ColumnCount; i++)
            {
 
                worksheet.Cells[1, i + 1] = myDGV.Columns[i].HeaderText;
            }
 
            //寫入數(shù)值
            for (int r = 0; r < myDGV.Rows.Count; r++)
            {
                for (int i = 0; i < myDGV.ColumnCount; i++)
                {
                    worksheet.Cells[r + 2, i + 1] = myDGV.Rows[r].Cells[i].Value;
                }
                System.Windows.Forms.Application.DoEvents();
            }
            worksheet.Columns.EntireColumn.AutoFit();//列寬自適應(yīng)
            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("導(dǎo)出文件時(shí)出錯(cuò),文件可能正被打開！\n" + ex.Message);
                }
            }
            xlApp.Quit();
            GC.Collect();//強(qiáng)行銷毀
            MessageBox.Show("文件： " + saveFileName + ".xls 保存成功", "信息提示",
            MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

CSV與DatagridView之間操作

csv文件導(dǎo)入DateGridView

        /// <summary>
        /// 將csv文件數(shù)據(jù)導(dǎo)入datagridview
        /// </summary>
        /// <param name="csvPath"></param>
        /// <returns></returns>
        public static void ImportCSV(DataGridView dgv)
        {
            string filePath;
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.Filter = "CSV files (*.csv)|*.csv";
            openFileDialog.FilterIndex = 0;
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                filePath = openFileDialog.FileName;
            }
            else
            {
                return;
            }
            System.Data.DataTable dt = new System.Data.DataTable();
            using (StreamReader sr = new StreamReader(filePath))
            {
                string[] headers = sr.ReadLine().Split(',');
                string headerValue = null;
                foreach (string header in headers)
                {
                    headerValue = header.Replace("\"", "");
                    dt.Columns.Add(headerValue);
                }
                while (!sr.EndOfStream)
                {
                    string[] rows = sr.ReadLine().Split(',');
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < headers.Length; i++)
                    {
                        dr[i] = rows[i].Replace("\"", "");
                    }
                    dt.Rows.Add(dr);
                }
            }
            dgv.DataSource = dt;
        }

DateGridView導(dǎo)出csv文件

       /// <summary>
       /// DateGridView導(dǎo)出到csv格式的Excel,通用  
       /// </summary>
       /// <param name="dgv"></param>
       public static void ExportToCSV(DataGridView dgv)
       {
           string filePath;
           SaveFileDialog saveFileDialog = new SaveFileDialog();
           saveFileDialog.Filter = "CSV files (*.csv)|*.csv";
           saveFileDialog.FilterIndex = 0;
           if (saveFileDialog.ShowDialog() == DialogResult.OK)
           {
               filePath = saveFileDialog.FileName;
           }
           else
           {
               return;
           }
           using (StreamWriter sw = new StreamWriter(filePath))
           {
               // 寫入列標(biāo)題
               string headers = string.Join(",", dgv.Columns.Cast<DataGridViewColumn>().Select(column => "\"" + column.HeaderText + "\"").ToArray());
               sw.WriteLine(headers);
 
               // 寫入數(shù)據(jù)行
               foreach (DataGridViewRow row in dgv.Rows)
               {
                   string line = string.Join(",", row.Cells.Cast<DataGridViewCell>().Select(cell => "\"" + cell.Value?.ToString().Replace("\"", "\"\"") + "\"").ToArray());
                   sw.WriteLine(line);
               }
           }
       }

到此這篇關(guān)于C#操作Excel的多種方式小結(jié)的文章就介紹到這了,更多相關(guān)C#操作Excel內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: