C# 操作Excel的多种方式
在项目开发过程和办公过程中，经常要遇到对Excel进行创建，读写等操作，excel太多师，整理也成了一大难题。通过程序对excel进行整理能快速提供开发和办公效率。
Excel操作几种方式
- 使用OleDb(过时)
- 使用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文件中第一个工作表的数据
string query = "SELECT * FROM [Sheet1$]";
using (OleDbCommand command = new OleDbCommand(query, connection))
using (OleDbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
// 假设第一列是字符串，第二列是整数
string stringValue = reader.GetString(0);
int intValue = reader.GetInt32(1);
Console.WriteLine($"String value: {stringValue}, Integer value: {intValue}");
}
}
}
catch (Exception ex)
{
Console.WriteLine($"错误: {ex.Message}");
}
}
}Microsoft.Office.Interop.Excel
通过Microsoft.Office.Interop.Excel COM组件操作Excel可以实现对Excel文件的高度控制，支持复杂的Excel操作。但对资源的管理需要谨慎处理，易造成资源泄漏且兼容性不高。
string excelFilePath = "D:\\Test\\test.xlsx";
private void OpenExcelByInteropExcel()
{
// 创建Excel应用程序对象
Excel.Application excelApp = new Excel.Application();
excelApp.Visible = true; // 可见Excel应用程序界面
// 打开Excel文件
Excel.Workbook workbook = excelApp.Workbooks.Open(excelFilePath);
Excel.Worksheet worksheet = workbook.Sheets[1] as Excel.Worksheet;
// 读取或写入数据
Excel.Range range = worksheet.UsedRange;
for (int row = 1; row <= range.Rows.Count; row++)
{
for (int column = 1; column <= range.Columns.Count; column++)
{
// 处理单元格数据
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);//复制模板Sheet1内容
//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跨平台，不依赖于Microsoft Office，适用于在服务器端等环境中操作Excel文件。支持读取和写入Excel文件，并提供了丰富的API。但对于复杂的Excel操作，不如COM组件灵活，无法实现一些高级功能。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) // 确保单元格不为空
{
// 处理单元格数据
var cellValue = currentCell.ToString();
Console.WriteLine(cellValue);
}
}
}
}
}
}使用OpenXml
使用OpenXml操作Excel，它直接对文件流进行操作，而无需将整个文档加载到内存中，无需安装 Microsoft Office，非常适合服务器端应用程序和批处理。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文件导入DateGridView
/// <summary>
/// Excel导入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导出Excel文件
/// <summary>
/// Datagridview导出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; //被点了取消
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装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
//写入标题
for (int i = 0; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[1, i + 1] = myDGV.Columns[i].HeaderText;
}
//写入数值
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();//列宽自适应
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
MessageBox.Show("文件： " + saveFileName + ".xls 保存成功", "信息提示",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}CSV与DatagridView之间操作
csv文件导入DateGridView
/// <summary>
/// 将csv文件数据导入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导出csv文件
/// <summary>
/// DateGridView导出到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))
{
// 写入列标题
string headers = string.Join(",", dgv.Columns.Cast<DataGridViewColumn>().Select(column => "\"" + column.HeaderText + "\"").ToArray());
sw.WriteLine(headers);
// 写入数据行
foreach (DataGridViewRow row in dgv.Rows)
{
string line = string.Join(",", row.Cells.Cast<DataGridViewCell>().Select(cell => "\"" + cell.Value?.ToString().Replace("\"", "\"\"") + "\"").ToArray());
sw.WriteLine(line);
}
}
}实例链接：https://download.csdn.net/download/lvxingzhe3/89977285
参考：
C# EXCEL创建，编辑，导出，按模板保存以及Excel与 DataGridView互转_c#创建excel文件-CSDN博客
C#NPOI应用（导入导出Excel）_c# npoi 操作excel-CSDN博客
使用 C# 和 OpenXML 读取大型 Excel 文件_openxml excel c#-CSDN博客
原文地址：https://blog.csdn.net/lvxingzhe3/article/details/143698231
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！