Excel是Microsoft Office中一款重要的软件,有强大的数据处理功能,用户可以通过Excel方便的操作和强大的公式以及图表处理现有的数据。
在Excel文档中,数据是有明确的标识的,一般由其行名称和列名称进行标识。在C#中向Excel文档写入数据时,Microsoft Excel X Object Library也提供了这种支持。只需明确地给出所需添加的位置,即可向Excel文档的指定位置添加数据。
在Excel文档中,数据是有明确的标识的,一般由其行名称和列名称进行标识。在C#中向Excel文档写入数据时,Microsoft Excel X Object Library也提供了这种支持。只需明确地给出所需添加的位置,即可向Excel文档的指定位置添加数据。
先创建一个空白Excel文件: E:\TEMP\myTestExcel.xlsx,和一个待读取文件: E:\TEMP\英超俱乐部.xls
其中,待读取文件内容如下图所示:
首先,添加如下引用:
具体代码如下:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
namespace ReadWriteExcel
{
/// <summary>
/// MainWindow.xaml 的交互逻辑
/// </summary>
public partial class MainWindow : System.Windows.Window
{
public MainWindow()
{
InitializeComponent();
}
//从Excel读取内容
private void btnReadExcel_Click(object sender, RoutedEventArgs e)
{
string readFilePathName = @"E:\TEMP\英超俱乐部.xls";
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + readFilePathName + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.DataTable dt = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
DataSet ds = new DataSet();
string tableName = dt.Rows[0][2].ToString().Trim();//获取Excel的表名,默认值是sheet1
//string strSql = "select * from [" + tableName + "]";
string strSql = "select * from [" + tableName + "]";
OleDbCommand cmd = new OleDbCommand(strSql, conn);
OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
da.Fill(ds);//填充数据
//遍历DataSet并输出其中的内容
foreach (DataRow col in ds.Tables[0].Rows)
{
Debug.WriteLine(col[1].ToString());//输出了第0列的所有单元格内容
}
}
//向Excel写入内容
private void btnWriteExcel_Click(object sender, RoutedEventArgs e)
{
Microsoft.Office.Interop.Excel.Application xapp = new Microsoft.Office.Interop.Excel.Application();
string filePathName = @"E:\TEMP\myTestExcel.xlsx";
Microsoft.Office.Interop.Excel._Workbook xbook=xapp.Workbooks.Open(filePathName,Missing.Value,
Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,
Missing.Value, Missing.Value,Missing.Value,Missing.Value);
Microsoft.Office.Interop.Excel.Worksheet xsheet = xbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range ran = xsheet.get_Range("F1", Missing.Value);
ran.Value2 = "allen(test)";
Microsoft.Office.Interop.Excel.Range rng2 = xsheet.get_Range("A4", Missing.Value);
rng2.Value2 = "10";
Microsoft.Office.Interop.Excel.Range rng3 = xsheet.get_Range("B4", Missing.Value);
rng3.Value2 = "20";
Microsoft.Office.Interop.Excel.Range rng4 = xsheet.get_Range("C4", Missing.Value);
rng4.Value2 = "30";
Microsoft.Office.Interop.Excel.Range rng5 = xsheet.get_Range("D4", Missing.Value);
rng5.Value2 = "40";
Microsoft.Office.Interop.Excel.Range rng6 = xsheet.get_Range("E4", Missing.Value);
rng6.Value2 = "50";
Microsoft.Office.Interop.Excel.Range rng7 = xsheet.get_Range("F4", Missing.Value);
rng7.Value2 = "60";
Microsoft.Office.Interop.Excel.Range rng8 = xsheet.get_Range("G4", Missing.Value);
rng8.Value2 = "70";
Microsoft.Office.Interop.Excel.Range rng9 = xsheet.get_Range("H4", Missing.Value);
rng9.Value2 = "80";
Microsoft.Office.Interop.Excel.Range rng10 = xsheet.get_Range("I4", Missing.Value);
rng10.Value2 = "90";
Microsoft.Office.Interop.Excel.Range rng11 = xsheet.get_Range("A5", Missing.Value);
rng11.Value2 = DateTime.Now.ToLocalTime().ToString("yyyy-MM--dd HH:mm");
xbook.Save();
xbook.SaveAs(@"C:\test.xlsx", 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);
MessageBox.Show("写入成功");
}
}
}
读取效果:
写入效果:
完!